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 ==>	Harder than average
Subject ==>	Power BI training

Use the CALCULATE function in DAX to apply filters to measures in Power BI Desktop.

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 More Filters.pbix file in the folder shown above. The report contains a matrix with a measure which counts the number of shows for each concert tour.

Create a measure which calculates the percentage of shows which took place in venues which have since been demolished. You can use the Venue[Demolition date] column to determine whether it has been demolished or not.

Add a measure to calculate the percentage of shows held in venues which have been closed but not yet demolished, and another measure to show the percentage of shows held in venues which are still open.

DAX measures displayed in a Power BI Desktop matrix

A surprisingly low percentage of demolished venues for this tour.

Create a measure to calculate the percentage of cancelled shows. The Cancelled column in the Show table has a value of 1 if a show was cancelled.

Create two more measures to calculate the number of shows which mention COVID in the Cancellation reason column as:

A percentage of shows.
A percentage of cancelled shows.

You can use the CONTAINSSTRING function to check if one string contains another. The function isn't case-sensitive and its syntax is:

CONTAINSSTRING( Text to look in, Text to look for )

Apply formatting and add the measures to the matrix visual.

A Power BI Desktop matrix visual displaying DAX measures

This was most people's feeling after the third lockdown was announced.

Create a measure to calculate the percentage of shows with unsold tickets. You can compare the Tickets available and Tickets sold columns to do this. Make sure to only compare rows which have a value in both columns.

Add two more measures to calculate the sum and average number of unsold tickets.

Add a final measure to calculate the percentage of unsold tickets.