DAXDAX Trainer

DAX Concept

Mastering Filter Context Removal with CALCULATE and ALL in DAX

Understand and apply the CALCULATE function to modify and remove filter contexts using ALL, thereby calculating contributions relative to different total scopes.

BeginnerCALCULATEALLDIVIDE

Concept Breakdown

The CALCULATE function is one of the most powerful and fundamental functions in DAX. It evaluates an expression in a modified filter context. This modification can involve adding new filters, overriding existing ones, or removing them entirely. The ALL function, when used as a filter argument within CALCULATE, removes all filters from a specified table or column, allowing the calculation to refer to the grand total or a total for a larger scope. This is crucial for 'percentage of total' calculations, where you need to compare a value against a total unconstrained by certain filters.

What You Will Learn

  1. Understand when to use CALCULATE, ALL, DIVIDE 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 Power BI file.

  2. Open the Power BI file and observe the existing matrix showing 'Sales by environment and centre type'.

  3. If it doesn't exist, create a base measure called `Total Sales` (e.g., `SUM('YourSalesTable'[SalesColumn])`).

  4. Create a new measure in the 'All measures' table named `All environment contribution`.

  5. Define this measure to calculate the ratio of `Total Sales` to `Total Sales` with the 'Environment' filter removed, using `CALCULATE` and `ALL`.

  6. Add the `All environment contribution` measure to your matrix visualization.

  7. Verify that the sum of `All environment contribution` for each row (representing a 'Centre Type') equals 100%.

  8. Create a second new measure in the 'All measures' table called `Grand total contribution`.

  9. Define this measure to calculate the ratio of `Total Sales` to `Total Sales` with both the 'Environment' and 'Centre Type' filters removed, using `CALCULATE` and `ALL` twice.

  10. Add the `Grand total contribution` measure to your matrix or a separate card visual.

  11. Verify that the grand total of `Grand total contribution` equals 100%.

  12. Save the Power BI file as 'No constraints'.

Starter DAX

Total Sales = SUM('Sales'[SalesAmount])

Expected Outcome

Two new measures. 'All environment contribution' will show the percentage of sales for a given environment within its centre type, summing to 100% per centre type. 'Grand total contribution' will show the percentage of sales for a given environment and centre type relative to the overall grand total, summing to 100% across all environments and centre types.

Checking your sign-in status...