Table of Contents
In Power BI, measures and filters are used to analyze and manipulate data in reports and dashboards. To create measures for multiple values, you can use DAX formulas to define a calculation that will be applied to all values in a column or table. This allows you to create a single measure that can be used for multiple values, making it more efficient and consistent. Similarly, to create filters for multiple values, you can use the “Select All” option in the filter pane or use DAX expressions to specify multiple values in a single filter. This makes it easier to view and analyze data for specific values in a report.
You can use the following syntax in DAX to create a measure that filters rows based on multiple values:
Sum of Points = CALCULATE ( SUM ( 'my_data'[Points] ), 'my_data'[Team] IN { "A", "C" } )
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 either A or C.
The following example shows how to use this syntax in practice.
Example: Create Measure and Filter on Multiple Values in Power BI
Suppose we have the following table in Power BI named my_data that contains information about various basketball players:
Suppose we would like to calculate the sum of values in the Points column only for the players who have a value in the Team column of A or C.
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] IN { "A", "C" } )
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 either team A or C is 259.
Note: In this example we filtered based on two values, but you can filter using even more values by simply including more values in the curly brackets following the IN statement.
Additional Resources
The following tutorials explain how to perform other common tasks in Power BI: