DAXDAX Trainer

DAX Concept

Conditional Data Omission in Matrix Visuals using CALCULATE, VALUES, and FILTER

Learn to create DAX measures that conditionally omit specific data categories (like environments) from calculations in a matrix visual, leveraging the CALCULATE, VALUES, and FILTER functions to manipulate filter context.

IntermediateCALCULATEVALUESFILTER

Concept Breakdown

The CALCULATE function is crucial for modifying filter context. By combining it with FILTER and VALUES, you can define measures that respond dynamically to the current context. In this exercise, FILTER(VALUES(Column)) creates a virtual table of the distinct values present in the current filter context for 'Column'. When combined with a condition like `Column <> "Value_to_omit"`, this effectively removes 'Value_to_omit' from the virtual table, causing CALCULATE to return BLANK or a modified sum when that value is in the filter context, thereby 'omitting' it from a matrix row or column.

What You Will Learn

  1. Understand when to use CALCULATE, VALUES, FILTER 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 provided `Wise Owl Power BI Exercises.zip` file.

  2. Open Power BI Desktop and load the `Purchases` table from the Excel workbook `Wise Owl Power BI Exercises.xlsx` found in the unzipped folder. Ensure the `Quantity` and `Environment` columns are available.

  3. Create a base measure named `Total Sales Quantity` to sum the `Quantity` column (e.g., `SUM(Purchases[Quantity])`).

  4. Create three new measures: `Sales Excluding Air`, `Sales Excluding Land`, and `Sales Excluding Water`.

  5. For each new measure, use the `CALCULATE` function in conjunction with `FILTER` and `VALUES` to modify the filter context, so that the measure's calculation specifically excludes the 'Air', 'Land', or 'Water' environment respectively, in the current context.

  6. Create a matrix visual. Place the `Environment` column on the 'Columns' pane. Place the `Total Sales Quantity` measure and your three new 'Excluding' measures on the 'Values' pane.

  7. Observe how the 'Excluding' measures display blank values in the cells corresponding to their respective omitted environments (e.g., `Sales Excluding Air` should be blank when the matrix column is 'Air').

  8. (Optional challenge): Create two additional measures using `SUMX` and `FILTER` to sum sales quantity for purchases costing at least £10 and less than £10.

  9. Save your Power BI file as 'DAX fun'.

Starter DAX

Total Sales Quantity = SUM(Purchases[Quantity])

Expected Outcome

A Power BI matrix visual displaying `Total Sales Quantity` alongside three new measures: `Sales Excluding Air`, `Sales Excluding Land`, and `Sales Excluding Water`. The 'Excluding' measures will show blank values in the cells corresponding to their respective omitted environments (e.g., `Sales Excluding Air` will be blank when the matrix column is 'Air').

Checking your sign-in status...