Concept Breakdown
The CALCULATE function is one of the most powerful and frequently used functions in DAX. It evaluates an expression in a modified filter context. This means you can specify new filters that will be applied during the calculation, or you can remove or override existing filters. CALCULATE is crucial for creating measures that perform calculations under different conditions than the current report context, such as 'Sales for Category A' regardless of the selected category.
What You Will Learn
Understand when to use CALCULATE 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 'Music Tours - Calculate Basic Filters.pbix' file.
Open the .pbix file. Observe the existing matrix showing a count of albums by artist.
In the 'Music Measures' table, create a new measure called 'Count Top 10 Albums' to count albums with a US Billboard 200 peak less than or equal to 10.
Create a new measure called 'Sum Sales Top 10 Albums' to sum US sales (m) for albums with a US Billboard 200 peak less than or equal to 10.
Create 'Count No. 1 Albums' to count albums reaching the number 1 spot.
Create 'Sum Sales No. 1 Albums' to sum sales for number 1 albums.
Display these new album measures in the existing matrix and sort it by 'Count No. 1 Albums' in descending order.
Apply appropriate formatting (e.g., currency) to the sales measures.
Create '% Top 10 Albums' by dividing 'Count Top 10 Albums' by the total count of albums.
Create '% No. 1 Albums' by dividing 'Count No. 1 Albums' by the total count of albums.
Apply percentage formatting to these new measures and display them in the matrix, sorting by '% No. 1 Albums' column.
Create track-related measures: 'Count Singles' (where 'Single release date' is not blank), 'Count Top 10 Singles' (peak <= 10), 'Sum Sales Top 10 Singles' (peak <= 10), 'Count No. 1 Singles' (peak = 1), and 'Sum Sales No. 1 Singles' (peak = 1).
Create a new matrix to display these track measures, sorting it by 'Count No. 1 Singles' in descending order.
Apply any appropriate formatting to the new measures.
Starter DAX
Count of Albums = COUNTROWS('Album')Expected Outcome
A Power BI report with two matrices. The first matrix displays album statistics (counts, sums of sales, and percentages for Top 10 and No. 1 albums) per artist, sorted by No. 1 albums. The second matrix displays similar statistics for singles, sorted by No. 1 singles. All sales and percentage measures will be appropriately formatted.