Concept Breakdown
The CALCULATE function is one of the most powerful and frequently used functions in DAX. It changes the context in which data is evaluated. Specifically, it can modify an existing filter context (by adding, overriding, or removing filters) to evaluate an expression. When a filter argument is provided, CALCULATE first removes any filters on the columns specified in the filter argument from the filter context and then applies the new filters specified by the arguments, effectively re-evaluating the expression under the new filter conditions.
What You Will Learn
Understand when to use CALCULATE, CONTAINSSTRING, AVERAGEX, DIVIDE in a Power BI model.
Practice the concept inside a real PBIX report rather than only reading syntax.
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.
Download and unzip the provided file 'Music Tours - Calculate Replace Filters.pbix'.
Open the 'Music Tours - Calculate Replace Filters.pbix' file in Power BI Desktop.
Navigate to Page 1 of the report, which displays a matrix of subgenres.
Create a new measure named `Avg Track Length` to calculate the average track length in seconds. This requires multiplying 'Track'[Track mins] by 60 and adding 'Track'[Track secs] before averaging.
Create another measure named `Avg track length vs. pop` that compares the `Avg Track Length` with the same value where the subgenre equals 'Pop'.
Add both `Avg Track Length` and `Avg track length vs. pop` to the matrix visual.
Apply conditional formatting to `Avg track length vs. pop` if desired.
Create a measure named `Avg track length vs. pop or rock` that compares the average track length for subgenres that are either 'Pop' or 'Rock'.
Create a measure named `Avg track length vs. all pop or rock` that compares the average track length for subgenres containing the word 'Pop' or 'Rock' (using `CONTAINSSTRING`).
Add these new measures to the matrix on Page 1 and apply conditional formatting as desired.
Navigate to Page 2 of the report, which displays a chart showing the percentage of cancelled shows by year.
Create a base measure `Total Shows` to count all shows.
Create a base measure `Cancelled Shows` to count only cancelled shows.
Create a base measure `% Cancelled Shows` to calculate the percentage of cancelled shows using `DIVIDE`.
Create a new measure named `% Cancelled (2020 or 2021)` to calculate the percentage of cancelled shows specifically for the years 2020 or 2021.
Create another measure named `% Cancelled (Excl. 2020 or 2021)` to calculate the percentage of cancelled shows excluding the years 2020 and 2021.
Add `% Cancelled (2020 or 2021)` and `% Cancelled (Excl. 2020 or 2021)` to the Y-axis bucket of the chart on Page 2.
Observe the difference in percentages on the chart.
Save and close the report.
Starter DAX
Avg Track Length =
AVERAGEX(
'Track',
'Track'[Track mins] * 60 + 'Track'[Track secs]
)Expected Outcome
On Page 1, the matrix will display the `Avg Track Length` for each subgenre, alongside comparative measures (`Avg track length vs. pop`, `Avg track length vs. pop or rock`, `Avg track length vs. all pop or rock`) which will show the average track length specifically filtered for 'Pop', 'Pop' or 'Rock', and subgenres containing 'Pop' or 'Rock' respectively, irrespective of the current row's subgenre. On Page 2, the chart will display the overall `% Cancelled Shows` by year, with additional lines showing the `% Cancelled (2020 or 2021)` and `% Cancelled (Excl. 2020 or 2021)` measures, highlighting specific year-based cancellation rates.