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.

The answer to the exercise will be included and explained if you attend the course listed below!

Software ==> Power BI  (88 exercises)
Version ==> Latest update
Topic ==> Parameters  (5 exercises)
Level ==> Average difficulty
Course ==> Advanced 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:

Parameters drop down

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 need to create the drop down list. Right-click Usual Treatment column:

New query dynamic drop down

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


By creating a separate query based on the original query we ensure that the drop down values will be the same as those in the original column.

Remove duplicates query editor drop down parameters

This list will contain each value multiple times, so it's worth right clicking and choosing Remove Duplicates.

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

Power BI Parameter drop downs

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!).

Try flicking between the different treatments using Edit Queries then Edit Parameters from the Home tab. Save as Dropping usual treatments.

This page has 0 threads Add post