This page has 0 threads | Add post

Exercises

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

Software ==>	DAX (59 exercises)
Topic ==>	Variables (3 exercises)
Level ==>	Average difficulty
Subject ==>	Power BI training

Use variables a DAX measures to break a long formula into separate steps.

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 - Variables.pbix file in the folder shown above. You'll find a table visual containing a list of music artists and a count of the shows they have performed.

A table visual in Power BI Desktop displaying a DAX measure.

The first two artists in the table.

The aim is to create a measure using variables which shows the percentage of unsold tickets for each artist. Create a new measure and use three variables to capture the following values (you can choose what to name your variables):

What to calculate
The total number of Tickets sold.
The total number of Tickets available.
The number of unsold tickets.

Add a RETURN statement to the measure which divides the unsold tickets by the available tickets. Apply some formatting and display this measure in the table:

A Power BI Desktop table visual displaying some DAX measures.

You may notice a discrepancy for one of the artists.

It shouldn't be possible to have a negative number of unsold tickets - the problem occurs because some shows have a value for Tickets sold but not for Tickets available:

The offending rows in the Show table.

To resolve this, modify the values stored in the first two variables so that they only include rows where both the Tickets sold and Tickets available fields are not blank.

A table visual in Power BI Desktop showing DAX measures

The corrected results.

Create another measure which shows the difference between each artist's percentage of unsold tickets and the overall percentage of unsold tickets. Use variables to help divide the calculation into manageable chunks.