This page has 0 threads | Add post

Exercises

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

Software ==>	DAX (59 exercises)
Topic ==>	Calculated columns (11 exercises)
Level ==>	Average difficulty
Subject ==>	Power BI training

Use conditional functions like IF, SWITCH, ISBLANK and AND to create calculated columns in a report on music tours.

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 - Conditional Functions.pbix file in the folder shown above and go to the Table view of the Tour table.

Add a calculated column called Duration which uses an IF function to test if the Start year is the same as the End year. If so, return Single year, otherwise return Multi year.

Create a matrix visual to display the results shown in the diagram below:

A matrix visual in a Power BI Desktop report.

Unsurprisingly, longer tours make more money.

Add a calculated column called Crowd size which uses the SWITCH function to describe the Average attendance according to the following table:

Average attendance	Description
Is blank	Unknown
Less than 25,000	Small
25,000 to less than 50,000	Medium
50,000 or more	Large

Use this new column in a slicer visual to filter a table of tours. The result could look something like the diagram shown below:

A slicer and table visual in Power BI Desktop.

Choosing different options in the slicer affects the values shown in the table.

Add a calculated column called Success which describes each tour according to the following criteria:

Criteria	Description
Actual gross is at least 200,000,000 AND Attendance is at least 2,000,000	Smash hit
Actual gross is at least 200,000,000 OR Attendance is at least 2,000,000	Modest success
Anything else	Abject failure