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
Tables
There are many, many ways to filter data with DAX. The simplest, most straight-forward way is the function FILTER(). You simply pass in the table, and then specify the arguments. Being a table function, this returns a table:
FILTER( VideoGamesList, VideoGamesList[Platform] = "Playstation")
There's another function called CALCULATETABLE(), which is very similar to FILTER(), but has some key differences.
CALCULATETABLE( VideoGamesList, VideoGamesList[Platform] = "Playstation")
Despite everything being the same (input parameters and output table), the behavior is indeed different and should be kept in mind when writing DAX code. You can read more about the difference between the two functions in my other article, What's the Difference Between CALCULATETABLE and FILTER?
Scalar Values
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().
To create a measure (not a column!), click on the ellipses on the table, and select New Measure.
Count of Role-Playing Games =
CALCULATE(
COUNTA( VideoGamesList[Game] ),
VideoGamesList[Genre]="Role-Playing"
)DAX is certainly complicated. I've written an article about a great book to get started on your DAX journey, the best book for learning DAX.
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.
Power Query is a rich formula language and can do a lot of things. Check out my other blog about Power Query functions to learn more interesting things it can do!
Cheers!
Comments