DAXDAX Trainer

DAX Concept

Create Basic DAX Measures for Music Tour Statistics

Learn to create fundamental DAX measures using COUNTROWS and SUM, and apply basic arithmetic operations to calculate key statistics from different tables.

IntermediateCOUNTROWSSUMDIVIDEbasic arithmetic operators (+*)

Concept Breakdown

DAX measures are calculations performed at query time, useful for aggregating data across multiple rows. COUNTROWS returns the number of rows in a table or rows remaining after filters have been applied. SUM calculates the sum of all numbers in a column. Measures can also combine these functions with arithmetic operators to derive new insights, such as averages or totals from multiple sources.

What You Will Learn

  1. Understand when to use COUNTROWS, SUM, DIVIDE, basic arithmetic operators (+, *, ) 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 'Music Tours - Basic Measures.pbix' file.

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

  3. Add a new measure named 'Count Track' to the 'Music Measures' table that counts the rows of the 'Track' table.

  4. Add the 'Count Track' measure to the card and table visuals.

  5. Create a new measure named 'Avg Album Tracks' that divides 'Count Track' by the existing 'Count Album' measure.

  6. Add the 'Avg Album Tracks' measure to the card visual only.

  7. Add a new measure named 'Total Track Length (Seconds)' that calculates the sum of ('Track mins' * 60) + 'Track secs'.

  8. Create another measure named 'Avg Track Length (Seconds)' that divides 'Total Track Length (Seconds)' by 'Count Track'.

  9. Add 'Total Track Length (Seconds)' and 'Avg Track Length (Seconds)' to the card and table visuals.

  10. Develop a final measure named 'Total US Sales (m)' that sums the 'US Sales (m)' field from the 'Album' table and adds it to the sum of the 'US Sales (m)' field from the 'Track' table.

  11. Display the 'Total US Sales (m)' measure in both the card and table visuals.

Starter DAX

Count Album = COUNTROWS('Album')

Expected Outcome

The Power BI report will display a card and a table visual, both updated with the new measures. The card will show a single value for each measure, while the table will show the measure values broken down by the album/track context, filtered by the slicer. All new measures (Count Track, Avg Album Tracks, Total Track Length (Seconds), Avg Track Length (Seconds), Total US Sales (m)) will be correctly calculated and displayed.

Checking your sign-in status...