DAXDAX Trainer

DAX Concept

Mastering Filter Context with REMOVEFILTERS in DAX

Understand and apply the REMOVEFILTERS function within CALCULATE to manipulate filter context, enabling calculations that ignore specific filters or all filters.

IntermediateREMOVEFILTERSCALCULATE

Concept Breakdown

The REMOVEFILTERS function in DAX is a powerful table function that removes filters from the specified tables or columns. When used within the CALCULATE function, it modifies the filter context for the expression, allowing you to perform calculations that ignore the current selection, or specific columns/tables in the filter context. This is crucial for 'all up' totals, percentages of totals, or fixed visual axes.

What You Will Learn

  1. Understand when to use REMOVEFILTERS, CALCULATE in a Power BI model.

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

  3. Complete a intermediate-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 'Music Tours - Calculate Remove Filters.pbix' file and open it.

  2. On Page 1, create a measure named 'Max Track Length Global' to calculate the maximum value of the 'Track length total secs' column, removing all filters.

  3. Use this 'Max Track Length Global' measure to set the maximum value of the X-axis for the chart on Page 1, ensuring the X-axis no longer changes when different artists are selected.

  4. On Page 2, create a measure named '% of Album Total' to calculate the percentage that 'Sum of track length total secs' contributes to the total for all tracks within the current album. This will require removing filters from the 'Track name' field.

  5. Create a measure named '% of Artist Total' to calculate the percentage that 'Sum of track length total secs' contributes to the total for all tracks by the current artist. This will require removing filters from the 'Album' field.

  6. Create a measure named '% of Grand Total' to calculate the percentage that 'Sum of track length total secs' contributes to the overall total of all tracks, removing all filters from the 'Tracks' table.

  7. Add these new percentage measures to the matrix on Page 2.

  8. Apply appropriate formatting (e.g., percentage format) to the new measures for better readability.

  9. Save and close the report.

Starter DAX

Total Track Length = SUM('Tracks'[Track length total secs])

Expected Outcome

On Page 1, the X-axis of the chart will display a fixed maximum value regardless of the artist selected. On Page 2, the matrix will correctly show three new columns: percentages of track length relative to the album total, the artist total, and the grand total, respectively.

Checking your sign-in status...