How to create Power BI Filters on Measures with an example?. Power BI Measures Filters are used to filter the measures (metric values). For example, we can use Power BI Filters on Measures to display the products with sales greater than the threshold, or less than average, etc.
How to use Power BI Filters on Measures
To demonstrate the Measure Filters in Power BI, we are going to use the Bar Chart that we created in our earlier post. As you can see from 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 are the list of options that are available for Measures filters in Power BI
- Show Items when the Value: Please select the operator that you want to use.
- Empty Text box: Please specify the value.
- And and Or: Same as AND and OR Operators SQL. Use this to concatenate multiple filters.
- Empty drop down list: Please select the second operator that you want to use.
- Empty Text box: Please specify the value for the second filter.
The following are 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 displayed.
- is less than or equal to: If it is less than or equal to the given value, then the record displayed.
- is 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 display.
- is not: If the record is not equal to the value, then the record display.
- is blank: It displays all the records with the blank values.
- is not blank: It displays all the records that contain 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 is displaying 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 is displaying the states whose sales amount is less than 1000
This time we are using the is blank operator. You can see that the report is blank because we don’t have any state that contains blank 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
From the screenshot below, you can see that the report is displaying 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.
