Power BI | Parameters exercise | Filter data models using drop down parameters

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.

You can learn how to do this exercise if you attend one of more of the courses listed below!

Software ==> Power BI  (92 exercises)
Version ==> Latest update
Topic ==> Parameters  (5 exercises)
Level ==> Average difficulty
Classroom ==> Advanced Power BI  /  Fast track Power BI
Online ==> Advanced Power BI  /  Fast track Power BI
Before you can do this exercise, you'll need to download and unzip this file (if you have any problems doing this, click here for help).

You need a minimum screen resolution of about 700 pixels width to see our exercises. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Rather than give an input box for parameters we instead want to create a drop down for filtering. Like this in fact:

Dropdown parameter

Choosing a treatment will filter the data brought through into the data model.

Load the data from the Excel sheet in the above folder. Before we create the parameter we first need to create the drop down list for it. Right-click the Usual Treatment column:

New query dynamic drop down

At the bottom of the list choose Add as New Query.


Now remove any duplicates from your list of usual treatments, and sort it:

Making a list

This list will contain each value multiple times, so right-click on it and choose Remove Duplicates (then sort the items into ascending order, as here).

You now have a query to use for populating a drop down. Create your parameter:

Creating drop-down parameter

Irritatingly you still must choose an initial Current Value (and even more annoyingly it doesn't even give you the drop down you made to help you choose!).

Filter your data to show only customers who have had the specified treatment:

Chosen treatment

Only show treatments which match the one set as the parameter value.

Try flicking between the different treatments using Edit Queries then Edit Parameters from the Home tab, to test that everything works, then save your report as Dropping usual treatments.

You can unzip this file to see the answers to this exercise, although please remember this is for your personal use only.
This page has 0 threads Add post