Slicer in Power BI is a form of filter that play vital role in designing customized charts, tables and reports on Power BI Desktop. With a slicer, users could choose the type of data they would like to see on the report, and it would reflect immediately. Moreover, Power BI has released a new feature which allow user to filter the column of a table using the New Parameter – Fields option. The ‘New parameter – Fields’ option was issued in preview by Microsoft in the May 2022 update. Previously it is relatively straight forward to apply filter on rows but in this article, we will demonstrate how to apply filter on columns.
Step 1: Create a table with sample data with some fields added into it. Here I have used Sample Superstore data for demonstration.
Step 2: To add new parameter, go to New Parameter under modelling tab and select Fields option. If you do not see Fields option in New Parameter, then go to File ->> Options and Settings ->> Options ->> Preview Features ->> Check Field parameters option ->> Click on OK. You may need to close and re-open Power BI for the change to take effect.
Step 3: Click on New Parameter Fields option under modelling tab. A pop-up window will open as shown below. Drag the columns as listed in your table from your dataset and give parameter a name like Select Column. Also check the checkbox of ‘Add Slicer to this page’ and then click on Create button.
Step 4: “Select Column” Slicer will appear on your report. Change the slicer selection setting ‘Select All’ to ON and ‘Multi-select with Ctrl’ to OFF. Now if you click on the slicer, nothing will happen to your table. Therefore, select the table, delete all columns and enter the new parameter created in columns sections as shown below.
Step 5: Now our table will re-appear. To check the column slicer, uncheck column ‘Quantity’ and ‘Unit Price’. You can see only selected columns will appear in table.
So, now you have the ability to filter data by both column and by rows. One point to note is that if you are looking to reduce the number of columns but wish to keep them in same order, this may be preferable to start with the field table and reduce it to columns as I showed previously. Otherwise, if you deselect all columns and then start adding columns, you can create your own sequence of columns, this may be preferable if you wish to see data in different order.
Happy Reading!!
