Power BI Measures Filters are used to filter the measures (metric values). For example, we can display the products with sales greater than threshold, or less than average etc. In this article we will show you, How to create Power BI Filters on Measures with example.
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 below screenshot, we used the State Province Name as an Axis field, 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. Following are the list of options that are available for Measures filtering
- Show Items when the Value: Please select the opertor that you want to use.
- Empty Text box: Please specify the value.
- And and Or: Same as AND and OR Operators in 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 second filter.
Following are the list of options available under the Show items when the value section:
- is less than: When this option is selected, it will check each records against the Value. And if it is less than the user specified value then, the record will be displayed.
- is less than or equal to: If it is less than or equal to the given value then, the record will be displayed.
- is greater than: Displays the records whose sales amount is greater than specified value
- is greater than or equal to: Displays the records whose sales amount is greater than or equal to given value.
- is: If the record matches exactly with the value then, the record will be displayed.
- is not: If the record is not equal to the value then, the record will be displayed.
- is blank: It will display all the records with blank values.
- is not blank: It will display all the records that contains no blank values.
For the demonstration purpose we selected is greater than as the operator, and 1000000 as the value.
Below report 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 screenshot 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. From the below screenshot 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 Sales Amount column. First filter will find the states with sales greater than 300000, and second filter will find the states whose sales is less than 2300000
We used AND operator to join those two filters
From the below screenshot you can see that, the report is displaying the states whose sales amount is greater than 300000 and less than 2300000
Below report is displaying the states whose sales amount is greater than 300000, or sales amount is less than 1000
