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 DAX CALCULATE function to replace existing context filters in Power BI visuals

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 Replace Filters.pbix file in the folder shown above. On Page 1 you should see a matrix containing a list of subgenres.

Create a measure to show the average track length in seconds. You'll need to find the average of an expression which multiplies Track mins by 60 and adds Track secs to it.

Create another measure to compare the average track length in seconds with the same value where the subgenre equals pop. Add these measures to the matrix and apply some conditional formatting.

A Power BI matrix visual displaying DAX measures with conditional formatting

A sample of the results you should see.

Create the following measures to compare the average track length in seconds with other subsets of subgenres:

Measure name	Compare with
Avg track length vs. pop or rock	Tracks whose subgenre is either pop or rock.
Avg track length vs. all pop or rock	Tracks whose subgenre contains the word pop or rock (you can use the CONTAINSSTRING function to do this).

Display these measures in the matrix and apply some conditional formatting if you like.

DAX measures with conditional formatting displayed in a Power BI matrix visual

A sample of the results you should see.

On Page 2 of the report you should see a chart showing the percentage of cancelled shows by year.

A line chart in Power BI Desktop displaying percentage of cancelled concerts per year using a DAX measure

Wonder what that enormous spike in 2020 is all about?

Create a measure to calculate the percentage of cancelled shows when the Show[Show date].[Year] is either 2020 or 2021 and another measure to calculate the same value excluding these years.

Add these measures to the Y-axis bucket of the chart.