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 CALCULATE and KEEPFILTERS functions in DAX to alter the behaviour of context filters in Power BI visuals.

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 - Calculate Keep Filters.pbix file in the folder shown above. You should see a matrix displaying a list of cities and the results of a measure.

We've tried to create a measure to calculate the average height of buildings but only for three specific cities. Sadly, it isn't quite working as intended:

A matrix visual in Power BI Desktop showing the results of a DAX measure.

Rather than showing only the cities we've specified, the matrix shows every city with the same value.

Edit the measure so that it returns the correct figure for only the three specified cities. You can wrap the KEEPFILTERS function around the filter in the measure to do this.

A filtered matrix visual in Power BI Desktop using the KEEPFILTERS DAX function

The matrix should change to show the correct results for only the specified cities.

Create a column chart visual and add the City field and the Avg height chosen cities measure to it.

A column chart in Power BI Desktop showing the results of a DAX measure

The chart will filter itself automatically to show only the specified cities.

Create a column chart and add the Building Name field to it. Create a measure which shows the sum of an expression which adds the Floors above ground to the Floors below ground field, but only for buildings which have the word tower in their name.

You'll need to combine the CALCULATE, SUMX, KEEPFILTERS and CONTAINSSTRING functions to do this.

Add the measure to the chart to see the results.