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

Use the DAX CALCULATE function to replace context filters.

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 - Replace Filters.pbix file in the folder shown above. The report contains a matrix showing a list of countries and the average height of buildings.

Create a measure called Avg height US which calculates the average height of buildings where the Country[Country] column equals United States. Display this measure in the matrix.

DAX measures displayed in a matrix visual in Power BI Desktop

The figure for United States should appear next to each country.

Create a measure called Avg height vs. US which subtracts Avg height US from Avg height. Display this measure in the matrix and apply some conditional formatting to it.

A Power BI matrix with conditional formatting applied to DAX measures

It should be easier to see which countries have taller or shorter buildings compared to the United States.

Create a measure called Avg floors which uses the AVERAGEX function to calculate the average of Floors above ground plus Floors below ground.

Create another measure called Avg floors US which calculates the same value for buildings in the United States.

Create another measure called Avg floors vs. US which works out the difference between the previous two measures. Display the Avg floors and Avg floors vs. US measures in the matrix and apply conditional formatting to the latter.

A matrix in Power BI Desktop with conditional formatting applied to DAX measures

Each country should be compared with the value for United States.

Create a measure called Total floors which uses the SUMX function to calculate the sum of Floors above ground plus Floors below ground.

Create another measure called Total floors US which calculates the same value for buildings in the United States.

Create another measure called Total floors as % of US which divides Total floors by Total floors US. Display the Total floors and Total floors as % of US measures in the matrix.