This page has 0 threads | Add post

Exercises

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

Software ==>	Power BI (160 exercises)
Topic ==>	Calculated columns (12 exercises)
Level ==>	Average difficulty
Subject ==>	Power BI training

Use conditional functions like IF, SWITCH 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

Remember that you can use the AND and OR functions, or the && and || operators to combine conditions.

Use the new column in a matrix visual to count the tours for each category.

A matrix and slicer visual in Power BI Desktop.

You can use the slicer you created earlier to filter the results.

Save the file as Failure is not an option.pbix

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

POWER BI EXERCISES

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

Head office

London

Manchester