This page has 0 threads | Add post

Exercises

Finding our exercises useful? Please consider helping us to share more!

Software ==>	DAX (59 exercises)
Topic ==>	The CALCULATE function (16 exercises)
Level ==>	Average difficulty
Subject ==>	Power BI training

Use the CALCULATE function in DAX measures to add filters to expressions.

This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.

Before you can do this exercise, you'll need to download and unzip this file .

Open the Music Tours - Calculate Basic Filters.pbix file in the folder shown above. The report contains a matrix showing a count of albums by each artist.

Add measures to the Music Measures table to calculate the following album statistics:

Measure	What it should do
Count Top 10 Albums	Count the albums whose US Billboard 200 peak is less than or equal to 10.
Sum Sales Top 10 Albums	Sum the US sales (m) field in the Album table for albums which made it into the top 10.
Count No. 1 Albums	Count the albums which reached the number 1 spot in the US Billboard 200 peak field.
Sum Sales No. 1 Albums	Sum the album sales for albums which reached number 1.

Display the new measures in the matrix and sort it so that the artist with the most number 1 albums is at the top.

A Power BI Desktop matrix displaying DAX measures.

Apply some appropriate formatting to the sales measures.

Create the following new measures:

Measure	What it should do
% Top 10 Albums	Divide the count of top 10 albums by the count of albums.
% No. 1 Albums	Divide the count of number 1 albums by the count of albums.

Apply appropriate formatting to the measures and display them in the matrix, sorting it by the % No. 1 Albums column.

Various DAX measures displayed in a matrix visual in Power BI Desktop.

Your matrix should now resemble this.

Create measures to calculate the following track statistics:

Measure	What it should do
Count Singles	Count tracks which were released as singles, i.e. where the Single release date field is not blank.
Count Top 10 Singles	Count tracks which were released as singles and whose US Billboard Hot 100 peak field is 10 or less.
Sum Sales Top 10 Singles	Sum the US sales (m) field in the Track table for singles which made it into the top 10.
Count No. 1 Singles	Count singles which made it to the top spot in the US Billboard Hot 100 peak field.
Sum Sales No. 1 Singles	Sum the US sales (m) field in the Track table for number 1 singles.

Create a new matrix in which to display the new measures you've created. Sort it in descending order of Count No. 1 Singles.

A Power BI Desktop matrix displaying various DAX measures

Apply any formatting you feel is appropriate.

Save and close the file.

You can unzip this file to see the answers to this exercise, although please remember this is for your personal use only.

You can find other training resources for the subject of this exercise here:

This page has 0 threads

Add a new post

EXERCISE TOPIC

POWER BI EXERCISES

DAX EXERCISES

Use the CALCULATE function in DAX measures to add filters to expressions.

Head office

London

Manchester