PowerBi Desktop Query Parameters

One of the coolest features added to the April 2016 release of Power BI Desktop is “Query Parameters”. With Query Parameters we can now create parameters in Power BI Desktop and use them in various cases. For instance, we can now define a query referencing a parameter to retrieve different datasets. Or we can reference parameters via Filter Rows. Generally speaking we can reference parameters via:

• Data Source
• Filter Rows
• Keep Rows
• Remove Rows
• Replace Rows

In addition, parameters can be loaded to the Data Model so that we can reference them from measures, calculated columns, calculated tables and report elements.

In “Power BI Desktop Query Parameters” series of articles I show you how to use Query Parameters in different scenarios.

Scenario

Parameterising a Data Source

Requirements

You’ll require to meet the following requirements to be able to follow this post:

1. The latest version of Power BI Desktop (Version: 2.34.4372.322 64-bit (April 2016) or later)
2. SQL Server 2016: Check this out to see how you can download SQL Server 2016 Developer edition for free.
3. Adventure Works 2016 CTP3

Note: As Dynamic Data Masking (DDM) is a new feature of SQL Server 2016 and it is not available in the previous versions of SQL Server you need to install the latest version of SQL Server 2016. So you will need SQL Server 2016 and Adventure Works CTP3 only if you want to use Query Parameters on top of Dynamic Data Masking (DDM).

Parameterising a Data Source

Parameterising a Data Source could be used in many different use cases. From connecting to different data sources defined in Query Parameters to load different combinations of columns. To make it more clear I break down the scenario to some more specific use cases.

Parameterising Data Source to Connect to Different Servers and Different Databases

Suppose you have different customers using the same database schema. But, the databases hosted in different instances of SQL Server and also the database names are different. With Query Parameters we can easily switch between different data sources then publish the reports to each customers’ Power BI Service.

• Open Power BI Desktop
• Click Get Data
• Select “Blank Query” from “Other” then click “Connect”
• In Query Editor window click “Manage Parameters” from the ribbon

1-Click “New”

2-Type a name for the parameter

3-You can also write a description

4-Select Type as Text

5-From “Allowed Values” select “List of Values”. This opens a list that you can type in different values for the parameter. If you don’t want to enter ant predefined values for the parameter select “Any value” for “Allowed Values”

6-Fill the list with some valid values. In our case it would be instance names

7, 8, 9-Select a “Default Value” and “Current Value” then click OK

• Now you can see the parameter in the Queries pane
• Create another parameter for database names. You can do this by clicking “Manage Parameters” from the ribbon, or you can right click on the “Queries” pane then “New Parameter”
•  
• Repeat the above 9 steps again, but, this time for defining a parameter for database names
  
• You should now see both parameters in Queries pane
• You can delete “Query1” as we want to use the above parameters in the Data Source dialogues
• Click “New Source” from the ribbon
• Click “SQL Server Database” then click “Connect”
• Select “Parameter” for “Server” then select the appropriate parameter from the list
• Do the same for “Database” then click OK
• Select “FactInternetSales”
• Click “Select Related Tables” then click OK
• Click “Close & Apply”
• So far we loaded data from “AdventureWorksDW2016CTP3” into the model. Now it’s time to create a simple report then switch the parameters to see how it works.
• As you see I added a column chart showing Sales Amount by Calendar Year and also a table showing Products and Sales Amount. (Just keep it simple)
• To switch the server/database we just need to change the parameters’ values
• Click “Edit Queries” then “Edit Parameters”
• As you see you can now simple change the “Instance Name” and the “Database” names to switch to another server and database without touching anything else then click OK

• The data source refreshes to load data from the new server/database