Power BI Desktop Query Parameters, Part 1

Power BI 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.

Scenarios

In this article I’ll show you some use cases of Query Parameters based on some scenarios as below:

  1. Parameterising a Data Source
  2. 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

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)

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
  • Select “Blank Query” from “Other” then click “Connect”Power BI Desktop Create Blank Query
  • In Query Editor window click “Manage Parameters” from the ribbon

Power BI Desktop Manage Parameters

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

Power BI Desktop Manage Parameters 02

  • Now you can see the parameter in the Queries panePower BI Desktop Query Parameters
  • 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”Power BI Desktop Create Query Parameters
  • Repeat the above 9 steps again, but, this time for defining a parameter for database namesPower BI Desktop Manage Parameters 03
  • 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 OKPower BI Desktop Data Source Query Parameters
  • Select “FactInternetSales”
  • Click “Select Related Tables” then click OKPower BI Desktop Get Data
  • Click “Close & Apply”Power BI Desktop Query Editor
  • 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)Power BI Desktop
  • To switch the server/database we just need to change the parameters’ values
  • Click “Edit Queries” then “Edit Parameters”Power BI Desktop 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

Power BI Desktop Enter Parameters

  • The data source refreshes to load data from the new server/databasePower BI Desktop Refresh DataPower BI Desktop Reports

Use Case 2: Loading Dynamic Columns from the Data Source

Remember the previous use case. We had different customers having different databases on different servers. Our customers also have different reporting needs. For instance, they need to see their customers’ names in different shapes. The customers data stored in DimCustomer. We should cover the combinations below for “Customer Name” column:

1- Customer Name = LastName from DimCustomer

2- Customer Name = FirstName + LastName from DimCustomer

3- Customer Name = LastName  + FirstName from DimCustomer

4- Customer Name = FirstName + MiddleName + LastName from DimCustomer

To support this we can create a parameter containing all combinations above for Customer Name.

  • Delete DimCustomer from the model we created for the previous use case
  • Create a new parameter and add all needed combinations in the list of values. You can do this by right clicking on the Queries pane then click “New Parameter” or by clicking on “Manage Parameters” from the ribbon

Note: You should put T-SQL syntax to create different combinations in the values list as the values will be used as a column in the data source query.T-SQL in Power BI Desktop Query Parameters

  • Now we need to import DimCustomer into the model again. (Remember, we removed DimCustomer in pervious steps)
  • Click “New Source” from the ribbon on Query Editor window
  • Click “SQL Server Database” then “Connect”
  • Select “Server” and “Database” parameters
  • Click “Advanced options”
  • Put the following SQL statement

SELECT customerkey,

       geographykey,

       customeralternatekey,

       title,

       –firstname,

       –middlename,

       lastname,

       namestyle,

       birthdate,

       maritalstatus,

       suffix,

       gender,

       emailaddress,

       yearlyincome,

       totalchildren,

       numberchildrenathome,

       englisheducation,

       spanisheducation,

       frencheducation,

       englishoccupation,

       spanishoccupation,

       frenchoccupation,

       houseownerflag,

       numbercarsowned,

       addressline1,

       addressline2,

       phone,

       datefirstpurchase,

       commutedistance

FROM   DIMCUSTOMER

Note: I took out “firstname” and “middlename” from the query. The “lastname” column will be replaced with the “CustomerName” parameter in the next steps.Import Data From SQL Server Parameters

  • Click OKData Import
  • Rename “Query1” to DimCustomerRenaming Query
  • Click “Advanced Editor” from the ribbon
  • Scroll right to find “lastname” column
  • Replace “lastname” column with the “CustomerName” parameter. To do so you just need to replace “lastname” with the following:

                  “&CustomerName&” as CustomerName

    Power BI Desktop Advanced Editor

    • Click Done
    • You’ll get a warning message, click “Edit Permission”

    Power BI Desktop Edit Permission

    • Click “Run”

    Power BI Desktop Native Database Query

    • You will see the CustomerName after running the query

    Power BI Desktop Dynamic Column

    • Click “Close & Apply”
    • Add a new Matrix to the report and put “SalesAmount” from FactInternetSales and “CustomerName” from DimCustomer

    Power BI Desktop Report

    • Now we want to switch the “CustomerName” parameter value to see what happens
    • Click “Edit Queries” from the ribbon then click “Edit Parameters”

    Power BI Desktop Edit Parameters

    • Change just the value of “CustomerName” then click OK

    Power BI Desktop Switch Parameters

    • We modified the query by modifying the columns, so we need to confirm running the query, so click Run

    Power BI Desktop Native Database Query

    • As you see we now have a new combination in Customer Name column

    Power BI Desktop Matrix

    • If we want to switch between the server/database and also the customer name we just need to switch the parameters’ values

    Power BI Desktop Enter Parameters

    Power BI Desktop Report

    The above cases might not be the best examples of using the parameters on a data source, but, you probably got the idea how powerful this new feature could be.

    Scenario 2: Using Query Parameters in Filter Rows

    As stated before, in addition to data sources, we currently can reference parameters via Filter Rows, Keep Rows, Remove Rows and Replace Values. In this section I explain how to use parameters in Filter Rows.

    • In the model we created in previous sections click “Edit Queries” from the ribbon
    • In Query Editor window create a new Query Parameter

    Power BI Desktop Create New Parameter

    • Select DimSalesTerritory from Queries pane

    Power BI Desktop Query Editor

    • Add a row filter on “SalesTerritoryCountry” column

    Power BI Desktop Filter Rows

    • Select “Parameter” for filter type then select “Countries” parameter from the drop down list then click OK

    Power BI Desktop Filter Rows Parameters

    • As you see DimSalesTerritory is filtered for the rows that their SalesTerritoryCountry is equal to “United States”

    Power BI Desktop Filterd Query

    • Now switching the parameter value will change the “Filter Row”
    • Click the “Countries” parameter from the Queries pane then change Current Value to Canada

    Power BI Desktop Parameter Switch Current Value

    • Select DimSalesTerritory again and you’ll see that the is changed

    Power BI Desktop Filtered Query

    • Parameterising Filter Rows is done!

10 thoughts on “Power BI Desktop Query Parameters, Part 1

  1. This is really nice article. Is there a way that an end user can use a filter to choose from a set of values that can be fed as a parameter, since the end user does not have the permission to use or click the ‘Edit Parameter’ choice on the main page.

    I was able to get the options of choose a set of values in the parameters as a Power BI developer but, how to get this ability to an end user to set a parameter from a list of values???

    1. Hi Kumar,
      Welcome to BIInsight.
      Query Parameters, are in Power Query layer so the end user do not have access to it.
      The reason behind it is simple, you never want to involve your end users with lots of complex logic and transformation running in background.
      The best way to achieve your goal is to handle it in DAX. If you can express more information I’m happy to help you achieving your goal.
      Cheers

    2. Kumar,
      You can access the parameters outside of the Power Query Editor by going to Home tab in the ribbon, then click the drop down arrow of the Edit Queries and select Edit Parameters. But I’m not sure if you can edit Parameters for published reports.

      1. Hi Forrest,

        Thanks for your reply.
        I suppose the “End user” is the report consumer who is using published Power BI reports/dashboards on web. As modifying the parameters’ values will cause data refresh it is NOT accessible by the end users. Therefore, using DAX, if applicable, is the only way to add that sort of interactivities to the reports.

        Hope that helps.

  2. Very good article! Thank you. I wonder though how one uses Query instead of List of values as Suggested values in parameter definition?

  3. Great Article. I was wondering if it is possible like in SSRS where the user that is accessing the report passes their username dynamically to the stored procedure so they are accessing the information that should only be viewed by that user”(using expression: =mid(User!UserID , InStr(User!UserID,”\”)+1))”, if this is possible in Power BI within the Advanced Editor.

    1. Hi Greg.
      Welcome to BIInsight.
      In Power BI you can manage security using Row Level Security (RLS) using USERNAME() and USERPRINCIPALNAME() functions in DAX.
      I encourage you to check this out.
      Cheers

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.