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.
In this article I’ll show you some use cases of Query Parameters based on some scenarios as below:
- Parameterising a Data Source
- Using Query Parameters in Filter Rows
You’ll learn more about Query Parameters in the next articles “Power BI Desktop Query Parameters, Part 2, SQL Server Dynamic Data Masking Use Case” and “Power BI Query Parameters, Part 3, List Output“
You’ll require to meet the following requirements to be able to follow this post:
- The latest version of Power BI Desktop (Version: 2.34.4372.322 64-bit (April 2016) or later)
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).
Scenario 1: 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.
Use Case 1: 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
- In Query Editor window click “Manage Parameters” from the ribbon