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 FILTER function (4 exercises)
Level ==>	Harder than average
Subject ==>	Power BI training

Use the FILTER function in a DAX measure to provide a filtered version of a table to other functions.

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 - More Filter Functions.pbix file in the folder shown above. On Page 1 you'll find a table showing a list of artists and data on their tours, shows and ticket sales.

Create a measure which counts the number of shows which were cancelled due to COVID. You can check if the Cancellation reason column contains the word COVID to do this.

A table visual in Power BI Desktop showing the results of several DAX measures

Not every artist had to cancel shows due to COVID.

Create a measure which counts the tours which had at least one show cancelled due to COVID. You can combine the COUNTROWS and FILTER functions to do this.

Display the measure in the table and a separate card visual.

A table and card visual in Power BI Desktop showing the results of several measures.

We've used a New Card visual here.

Create measures to count the rows of the Show table and sum the Tickets sold column for tours which had at least one show cancelled due to COVID. You can combine the CALCULATE and FILTER functions to do this. Show the results in the table and card visuals.

Table and card visuals in Power BI Desktop showing the result of DAX measures.

A sample of the results you should see.

On Page 2 you'll find a table showing artists and information about albums and tracks.

Create a measure to count the tracks which reached number one in the charts. You can use the value of the US Billboard Hot 100 peak column to do this.

Create another measure to count albums which have at least one number one track. Display these measures in the table.

A table visual in Power BI Desktop displaying several DAX measures.

Some artists have no tracks which reached the top chart position.

Create measures to calculate the following values:

What to calculate
The count of all tracks from albums with at least one number one track.
The sum of track length for all tracks on albums with at least one number one track.
The average peak chart position of tracks.
The average peak chart position of tracks on albums with at least one number one track.

Display these measures in the table.