Power BI: How to Ignore Filter for a Measure


You can use the ALL function when creating a measure in Power BI to ignore filters in a report view.

For example, you can use the following syntax in DAX to create a measure named Max Points that calculates the max value in the Points column of a table while ignoring any filters on the Team column:

Max Points = CALCULATE(MAX('my_data'[Points]),ALL('my_data'[Team]))

The following example shows how to use this syntax in practice.

Example: How to Ignore Filter for a Measure in Power BI

Suppose we have the following table in Power BI named my_data that contains information about points scored by basketball players on various teams:

Suppose we would like to calculate the max value in the Points column.

To do so, click the Table tools tab along the top ribbon, then click the New measure icon:

Now suppose we type the following formula into the formula bar:

Max Points = MAX('my_data'[Points]) 

This will create a new measure named Max Points that contains the max value in the Points column of the table:

Power BI calculate max

Now suppose we switch to the report view and add a card that displays the value of this measure:

We can see that the max value in the Points column is 34.

However, if we filter to only show the players on the Mavs team then the value shown as the max changes to 22 since this represents the max points value among all players on the Mavs team:

In order to create a measure that ignores this filter, we must use the following syntax instead:

Max Points = CALCULATE(MAX('my_data'[Points]),ALL('my_data'[Team])) 

This will create a new measure named Max Points that contains the max value in the Points column of the table and ignores any filters on the Team column:

Now if we filter once again for the Mavs team in the report view, then the max value shown in the card visualization will still be 34 since this represents the max of all values in the Points column:

Note: You can find the complete documentation for the CALCULATE function in DAX here.

Additional Resources

The following tutorials explain how to perform other common tasks in Power BI:

How to Count Number of Occurrences in Power BI
How to Calculate Standard Deviation in Power BI
How to Calculate a Cumulative Sum in Power BI

Leave a Reply

Your email address will not be published. Required fields are marked *