Power BI: How to Filter
Updated: Aug 16, 2020
How to filter in Power BI? There are several methods of doing this depending on what you are wanting.
Inserting a slicer widget
Using a visual to filter
Using DAX to filter
Using Power Query to filter
1) Inserting a slicer widget
This is the most common way and allows your users full control over what they want to filter. The slicer widget can be found in the visuals 'bucket'.
Depending on the data type that you drag into your filter, the widgets' behavior will change.
Normal Text field - This will simple display a list of unique text values that are in the column.
Date field - By default this will be displayed as a slider bar. You can also change this to a drop-down selector by toggling the hierarchy function (select the down arrow, then select date hierarchy).
2) Using a visual to filter
This is the most fun way of filtering (in my humble opinion). By using a visual to filter, you can see how the selected filter compares against the entire dataset - other visuals will highlight the selected value and grey out the rest.
3) Using DAX to filter
If you want to do a SUMIF or COUNTIF (anything with an "IF") using an Excel-like formula, you can create a Measure and insert a DAX formula. The specific formula is CALCULATE().
DAX calls these "if" formulas "filters" - because it is literally filtering the data depending on the "if" statement.
To create a measure (not a column!), click on the ellipses on the table, and select New Measure.
Measure formula:
Count of Role-Playing Games = CALCULATE(COUNTA(VideoGamesList[Game]),VideoGamesList[Genre]="Role-Playing")4) Using Power Query to filter
Last but not least, we can use Power Query to filter.
Power Query is the tool that grabs the data from your sources. When you initially tell Power BI to "Get Data", it is actually Power Query that is getting the data for you.
When Power Query is grabbing the data for us, you can tell it to filter the data (i.e. prep the data), before it's loaded into Power BI. Any data that is filtered out will not appear in your Power BI dashboard.
To open Power Query, select "Transform Data" from the Home tab. From there, simply using the filter arrows on the table like you would normally in Excel.
After applying a filter, you'll notice a new step was added under the "Applied Steps" window. To remove the filter, simply click the "X" to delete the step.
Cheers!
