How to apply filters for a measure created (with aggregation) in Power BI?

Modified on Wed, 16 Oct 2019 at 06:15 AM

When you use Power BI in direct query mode and create an aggregate measure, there might be a chance that the resultant data could mismatch, when the filters are applied to the other columns in the visualization. 


Step-1: You can create a measure by clicking on the ... at the end of the table name. Select the New measure option. 


Step-2: Provide a name to the measure and the relevant formula (aggregation) for the measure to be created. 


Step-3: Select column(s) from the table and the created measure.


Step-4: Apply filters to a column(s) to view the aggregations based on the applied filters.


Here, even after the filters being applied, the data of the created measure seems to be the same. This is because Power BI sends two queries, one for the selected columns and the other for the created measure and the filters might not be applied properly to the created measure when the second query is being sent. This is due to the default Power BI behavior.


However, filters can be applied to the aggregate measures created. Even in this case, two queries will be sent, one to retrieve the columns matching the condition and the other to retrieve the data. Since the filters are applied to the created measure, it will be applied properly and the correct data will be retrieved.


Step-1: Select column(s) from the table and the created measure.


Step-2: Now apply filters to the created measure. The filter will be applied to the created measure and the correct data will 

be retrieved.

For more information on applying filters to a measure, please refer to the 'Measure filters' section in the following article: Direct Query report design guidance