Table of Contents
Creating a measure in Power BI with multiple filter conditions allows users to analyze and visualize data based on specific criteria. This can be done by using the DAX language to define the measure and applying multiple filter conditions to it. By doing so, users can effectively narrow down their data and gain more insights from their visualizations. This feature in Power BI is particularly useful for businesses and organizations looking to make data-driven decisions and understand their data in a more detailed manner.
Power BI: Create Measure with Multiple Filter Conditions
You can use the following syntax in DAX to create a measure that filters rows based on multiple conditions:
Method 1: Create Measure by Filtering with AND Condition
Sum of Points = CALCULATE ( SUM ( 'my_data'[Points] ), 'my_data'[Team] = "A" && 'my_data'[Position] = "Guard" )
This particular example creates a new measure named Sum of Points that calculates the sum of the values in the Points column only for the rows where the Team column is equal to “A” and the Position column is equal to “Guard.”
Method 2: Create Measure by Filtering with OR Condition
Sum of Points = CALCULATE ( SUM ( 'my_data'[Points] ), 'my_data'[Team] = "A" || 'my_data'[Position] = "Guard" )
This particular example creates a new measure named Sum of Points that calculates the sum of the values in the Points column only for the rows where the Team column is equal to “A” or the Position column is equal to “Guard.”
The following examples show how to use each method in practice with the following table in Power BI named my_data:
Example 1: Create Measure by Filtering with AND Condition
Suppose we would like to calculate the sum of values in the Points column for the players who are on Team A and have a Position of Guard.
To do so, click the Table tools tab and then click the New measure icon:
Then type the following formula into the formula bar:
Sum of Points = CALCULATE ( SUM ( 'my_data'[Points] ), 'my_data'[Team] = "A" && 'my_data'[Position] = "Guard" )
We can view this measure by switching to the Report View and inserting a card visualization that displays the value of the measure:
We can see that the sum of points for the players who are on Team A and have a Position of Guard is 36.
Example 2: Create Measure by Filtering with OR Condition
Suppose we would like to calculate the sum of values in the Points column for the players who are on Team A or have a Position of Guard.
To do so, click the Table tools tab and then click the New measure icon:
Then type the following formula into the formula bar:
Sum of Points = CALCULATE ( SUM ( 'my_data'[Points] ), 'my_data'[Team] = "A" || 'my_data'[Position] = "Guard" )
We can view this measure by switching to the Report View and inserting a card visualization that displays the value of the measure:
We can see that the sum of points for the players who are on Team A or have a Position of Guard is 193.
Additional Resources
The following tutorials explain how to perform other common tasks in Power BI: