Power BI: How to Use SUMMARIZE with FILTER


You can use the following syntax in DAX to use the SUMMARIZE function with the FILTER function:

Summary Table = FILTER(
        SUMMARIZE(my_data, my_data[Team], my_data[Points], my_data[Position]),
        my_data[Team] = "A")

This particular formula creates a new table named Summary Table that contains the Team, Points and Position values from the table named my_data, filtered on the rows where the value in the Team column is equal to A.

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

Example: How to Use SUMMARIZE with FILTER in Power BI

Suppose we have the following table named my_data that contains information about various basketball players:

Suppose that we would like to use the SUMMARIZE and FILTER functions to create a new table that displays the values from the Team, Position and Points columns only for the players on Team A.

To do so, click the Table tools tab, then click the New table icon:

Then type the following formula into the formula bar:

Summary Table = FILTER(
        SUMMARIZE(my_data, my_data[Team], my_data[Points], my_data[Position]),
        my_data[Team] = "A")

A new table named Summary Table will be created that contains the values from the three columns in the original table, filtered for the rows where the Team column is equal to A:

Power BI use SUMMARIZE with FILTER

Note that you can also filter on multiple conditions if you’d like.

For example, you could use the following syntax to filter the original table for rows where the Team column is equal to A and the Points column is greater than 20:

Summary Table = FILTER(
        SUMMARIZE(my_data, my_data[Team], my_data[Points], my_data[Position]),
        my_data[Team] = "A" && my_data[Points] > 20)

This will produce the following table:

Feel free to filter using as many conditions as you would like.

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

Additional Resources

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

Power BI: How to Calculate Average Value in Column
Power BI: How to Calculate Average and Ignore Zeros
Power BI: How to Use SUM IF

Leave a Reply

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