This article shows how to create Power BI Filters on Measures with an example. The Measures Filters are useful to filter the measures (metric values). For instance, we can use Power BI Filters on Measures to display the products with sales greater than the threshold, less than average, etc.
How to use Power BI Filters on Measures
To demonstrate the Measure Filters in Power BI, we use the Bar Chart we created in our earlier post. As shown in the Power BI screenshot below, we used the State Province Name as an Axis field. And we used the Sales amount as a Value field and Order Quantity as Color saturation.
Under the Filters section, you can see the list of available Power BI Filters on Measures.
Let me expand the Sales Amount to see the filter options. The following is the list of options available for Measures filters in Power BI.
- Show Items when the Value: Please select the operator you want to use.
- Empty Text box: Please specify the value.
- And Or: Same as AND and OR Operators SQL. Use this to concatenate multiple filters.
- Empty drop down list: Please select the second operator you want to use.
- Empty Text box: Please specify the value for the second filter.
Show Items Options
The following is the list of Power BI Filters on Measures options available under the Show items when the value section:
- is less than: When this option is selected, it checks each record against the Value. And if it is less than the user-specified value, then the record will display.
- is less than or equal to: If it is less than or equal to the given value, the record is displayed.
- greater than: Displays the records whose sales amount is greater than the specified value
- is greater than or equal to: Displays the records whose sales amount is greater than or equal to a given value.
- is: If the record matches exactly with the value, then the record will display.
- not: If the record is not equal to the value, then the record is displayed.
- is blank: This option displays all the records with empty values.
- is not blank: It displays all the records with no blank values.
For these Measure Filters in Power BI demonstration purpose, we selected is greater than as the operator and 1000000 as the value.
The report below displays the states whose Sales Amount is greater than 1000000.
Let me change the operator to is less than and value as 1000. From the below image, you can see that the report displays the states whose sales amount is less than 1000
This time we are using the is the blank operator. So you can see that the report is blank because we don’t have any state that contains empty sales.
Let me use multiple filters on the Sales Amount column. The First filter finds the states with sales greater than 300000, and the second filter finds the states whose sales are less than 2300000.
We used AND operator to join those two filters
The screenshot below shows that the report displays the states whose sales amount is greater than 300000 and less than 2300000.
The report below is displaying the states whose sales amount is greater than 300000 or the sales amount is less than 1000.