How to create Power BI filters on measures with an example? You use Power BI measure filters to filter measures (metric values). For example, we can use Power BI filters on measures to show products with sales above the threshold or below the average, etc.
How to use Power BI filters in measures
To demonstrate measure filters in Power BI, we'll use the bar chart we created in our previous post. As you can see in the Power BI screenshot below, we used the name of the state province as an axis field. And we use the sales amount as a value field and the order quantity as the color saturation.
In the Filters section, you can see the list of Power BI filters available on measures.
Let me expand the sales amount to see the filter options. The following is the list of options that are available for measure filters in Power BI.
- Show items when value: Select the operator you want to use.
- Empty text box: Specify the value.
- And me: Same as the AND and OR SQL operators. Use this to concatenate multiple filters.
- Empty dropdown list: Select the second operator you want to use.
- Empty text box: Specify the value of the second filter.
Here is the list of Power BI filter options on measures available in the Show items when value section:
- it's less than: When this option is selected, it verifies each record with the Value. And if it is less than the value specified by the user, then the record is displayed.
- is less than or equal to: If it is less than or equal to the given value, then the record is displayed.
- is greater than: Shows records whose sales amount is greater than the specified value
- is greater than or equal to: Shows records whose sales amount is greater than or equal to a given value.
- is: If the record exactly matches the value, then the registration screen.
- It is not: If the record is not equal to value, then the registration screen.
- it is blank: Show all records with blank values.
- not blank: Shows all records that do not contain blank values.
For this purpose of demonstrating measure filters in Power BI, we select is greater than as operator and 1000000 as value.
The following report shows the statuses whose Sales Amount is greater than 1,000,000.
Let me change the operator to it's less than and value as 1000. In the image below, you can see that the report shows the statuses whose sales amount is less than 1000
This time we are using the it is blank operator. You can see that the report is blank because we do not have any status that contains blank sales.
Let me use several filters on the Sales Amount column. The first filter searches for states with sales greater than 300,000, and the second filter searches for states with sales less than 2,300,000.
We use the AND operator to join those two filters
In the screenshot below, you can see that the report shows the statuses whose sales amount is greater than 300000 and less than 2300000.
The report below shows the statuses whose sales amount is greater than 300,000 or the sales amount is less than 1,000.