DAXDAX Trainer

DAX Concept

Applying Filters to Measures Using CALCULATE in DAX

Create robust measures using CALCULATE to apply various filters and calculate conditional aggregations, such as percentages based on venue status, show cancellations, and ticket sales.

IntermediateCALCULATECONTAINSSTRINGDIVIDEISBLANK

Concept Breakdown

The CALCULATE function is one of the most powerful and versatile functions in DAX. It evaluates an expression in a modified filter context. This modification allows you to override existing filters or add new ones, enabling advanced calculations like 'percentage of total' or conditional aggregations based on specific criteria that might not be present in the initial visual's filter context.

What You Will Learn

  1. Understand when to use CALCULATE, CONTAINSSTRING, DIVIDE, ISBLANK 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 'Music Tours - Calculate More Filters.pbix' file.

  2. Open the '.pbix' file in Power BI Desktop.

  3. Identify the existing matrix visual which displays a measure counting the number of shows for each concert tour.

  4. Create a new measure named `Percentage of Demolished Venue Shows` that calculates the percentage of all shows which took place in venues that have a `Demolition date` (i.e., `NOT ISBLANK('Venue'[Demolition date])`).

  5. Create a new measure named `Percentage of Cancelled Shows` that calculates the percentage of all shows where the `Cancelled` column in the `Show` table has a value of `1`.

  6. Create a new measure named `Percentage of COVID-Related Shows` that calculates the percentage of all shows where the `Cancellation reason` column in the `Show` table contains the string "COVID" (using `CONTAINSSTRING`).

  7. Create another new measure named `Percentage of Cancelled COVID Shows` that calculates the percentage of cancelled shows where the `Cancellation reason` column contains "COVID".

  8. Create a new measure named `Percentage of Shows with Unsold Tickets` that calculates the percentage of shows where the `Tickets available` column is greater than the `Tickets sold` column, ensuring both columns have non-blank values for the comparison.

  9. Create two additional measures: `Sum of Unsold Tickets` to calculate the total number of unsold tickets and `Average of Unsold Tickets` to calculate the average number of unsold tickets.

  10. Create a final measure named `Percentage of Unsold Tickets` which calculates the overall percentage of unsold tickets out of all tickets available.

  11. Apply appropriate formatting (e.g., percentage, currency, whole number) to all new measures and add them to the matrix visual.

  12. Save and close the report upon completion.

Starter DAX

CALCULATE(
    [Your Base Measure],
    <Table or Column Filter Expression>
)

Expected Outcome

A Power BI matrix visual enhanced with new measures displaying various percentages (e.g., of shows in demolished venues, cancelled shows, COVID-related shows, and shows with unsold tickets) alongside the total and average count of unsold tickets, categorized by concert tour. Values will reflect the impact of the applied filters on the base show count or ticket numbers.

Checking your sign-in status...