DAXDAX Trainer

DAX Concept

Controlling Filter Context with CALCULATE and KEEPFILTERS in DAX

Learn how to use CALCULATE and KEEPFILTERS to precisely control filter context within measures, ensuring calculations apply to specific data subsets while respecting or overriding existing filters.

BeginnerCALCULATEKEEPFILTERSSUMXCONTAINSSTRING

Concept Breakdown

CALCULATE is the most powerful DAX function, allowing you to modify the filter context in which an expression is evaluated. When CALCULATE is used, any filters specified within its filter arguments will override existing filters on the same columns. KEEPFILTERS, when used within a CALCULATE filter argument, alters this behavior; instead of overriding, it ensures that the new filter is intersected with the existing filter context for the specified column(s). This allows you to apply additional filters while still honoring existing ones from the visual or report page.

What You Will Learn

  1. Understand when to use CALCULATE, KEEPFILTERS, SUMX, CONTAINSSTRING in a Power BI model.

  2. Practice the concept inside a real PBIX report rather than only reading syntax.

  3. Complete a beginner-level task and verify the result with a hidden answer check.

Practice in Power BI

The starter PBIX link has not been attached yet. The student workflow is ready for the admin to connect the file.

  1. Download and unzip the provided 'Tallest Buildings - Calculate Keep Filters.pbix' file and open it in Power BI Desktop.

  2. Observe the matrix visual, which currently displays the 'Avg height chosen cities' measure. Notice how it shows the same average height for every city instead of filtering for specific ones.

  3. Locate and edit the existing 'Avg height chosen cities' measure. Wrap the KEEPFILTERS function around the filter argument that specifies the cities (e.g., `KEEPFILTERS ( 'Buildings'[City] IN { "Dubai", "Shanghai", "Mecca" } )`).

  4. Verify that the matrix visual now correctly displays results only for the specified cities, with other cities either removed or showing BLANK.

  5. Create a new Column Chart visual. Add the 'City' field to the Axis and the (fixed) 'Avg height chosen cities' measure to the Values. Observe that the chart automatically filters to show only the specified cities.

  6. Create another new Column Chart visual. Add the 'Building Name' field to the Axis.

  7. Create a new measure named 'Total Floors for Towers'.

  8. Define the 'Total Floors for Towers' measure using a combination of CALCULATE, SUMX, KEEPFILTERS, and CONTAINSSTRING. This measure should calculate the sum of ('Floors above ground' + 'Floors below ground') for buildings whose names contain the word "tower".

  9. Add the 'Total Floors for Towers' measure to the Values of the second Column Chart visual.

  10. Review the results in the chart, observing how the measure filters and calculates only for buildings containing "tower" in their name.

  11. Save and close the report.

Starter DAX

Avg height chosen cities = 
VAR TargetCities = { "Dubai", "Shanghai", "Mecca" } // Adjust these cities to match your PBIX file's data
RETURN
    CALCULATE (
        AVERAGE ( 'Buildings'[Height (m)] ), // Adjust 'Buildings' and 'Height (m)' to your table and column names
        'Buildings'[City] IN TargetCities // Adjust 'City' to your column name
    )

Expected Outcome

The initial matrix visual will transform to show average heights only for a specific list of cities (e.g., Dubai, Shanghai, Mecca), returning BLANK or excluding other cities. The first column chart will visually represent these specific cities and their average heights. The second column chart will display the sum of total floors for buildings whose names explicitly contain the word "tower", effectively filtering the dataset based on this text condition.

Checking your sign-in status...