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 ==>	Relatively easy
Subject ==>	Power BI training

Use conditional functions like IF and SWITCH to add calculated columns to a Power BI data model of tallest buildings..

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

Add a calculated column called Has basement which tests if the value of the Floors below ground column equals 0. If so, the formula should return No, otherwise it should return Yes. You can see the syntax of the IF function below:

Column Name = IF( Logical test, Answer if true, Answer if false )

Create a matrix visual which shows the number of buildings which do or do not have a basement:

Your matrix could resemble this one.

Add a calculated column called Century which uses an IF function to show the century in which the building was opened. A building which opened before the year 2001 belongs to the 20th Century (our list doesn't include any earlier buildings), otherwise it belongs to the 21st Century.

Use a matrix to display the values shown in the diagram below:

Buildings are getting bigger.

Create a calculated column called Size which uses the SWITCH function to assign each building to a category according to the following table:

Height m	Category
Less than 400	Tiny
400 to less than 500	Small
500 to less than 600	Medium
600 to less than 700	Big
700 or more	Massive

Remember that you can set the first argument of the SWITCH function to TRUE() to allow you to write a sequence of logical tests and answers.

Use a chart visual to show the number of buildings and average of total floors for each size category.

A clustered column chart in Power BI Desktop.

Despite some wild exaggerations, Trump Tower isn't bigly enough to warrant its own category.

Create a calculated column called Location which tests if the Coordinates column is blank. If so, the formula should return Unknown location, otherwise it should return the value of the Coordinates column.