DAXDAX Trainer

DAX Concept

Implementing the FILTER Function for Conditional Aggregations in DAX

Learn how to effectively use the `FILTER` function within an iterator like `AVERAGEX` to perform calculations on a dynamically filtered subset of a table.

BeginnerFILTERAVERAGEX

Concept Breakdown

The `FILTER` function in DAX is a table function that returns a table. It takes two arguments: a table and a filter expression. For each row in the input table, the filter expression is evaluated. Only rows for which the expression evaluates to TRUE are included in the resulting table. When `FILTER` is nested within an iterator function (e.g., `SUMX`, `AVERAGEX`, `COUNTX`), it first creates a filtered table based on the specified condition. The outer iterator then performs its calculations row-by-row over this newly filtered table, allowing for flexible conditional aggregations.

What You Will Learn

  1. Understand when to use FILTER, AVERAGEX 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 file "Tallest Buildings - Filter Function.pbix".

  2. Open the "Tallest Buildings - Filter Function.pbix" file in Power BI Desktop. You will see a chart showing the average number of floors for buildings in different countries.

  3. Create a new measure named "Average Floors Before 2010" which calculates the average number of floors for buildings that opened before the year 2010, using a combination of `AVERAGEX` and `FILTER`.

  4. Create another new measure named "Average Floors Since 2010" which calculates the average number of floors for buildings that opened since (including) the year 2010, also using `AVERAGEX` and `FILTER`.

  5. Add both newly created measures to the existing chart.

  6. Observe the updated chart, which should now display the average number of floors for both categories alongside the original average.

  7. Save and close the report.

Starter DAX

New Measure = 
AVERAGEX(
    FILTER(
        'YourTable',
        'YourTable'[YourYearColumn] < 2010
    ),
    'YourTable'[ValueColumn]
)

Expected Outcome

The chart will display the average number of floors per country, showing two new series: one representing buildings opened before 2010 and another for buildings opened since 2010.

Checking your sign-in status...