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:
-
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“
Requirements
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
-
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
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.
-
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.
-
Click OK
-
Rename “Query1” to DimCustomer
-
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
- Click Done
- You’ll get a warning message, click “Edit Permission”
- Click “Run”
- You will see the CustomerName after running the query
- Click “Close & Apply”
- Add a new Matrix to the report and put “SalesAmount” from FactInternetSales and “CustomerName” from DimCustomer
- Now we want to switch the “CustomerName” parameter value to see what happens
- Click “Edit Queries” from the ribbon then click “Edit Parameters”
- Change just the value of “CustomerName” then click OK
- We modified the query by modifying the columns, so we need to confirm running the query, so click Run
- As you see we now have a new combination in Customer Name column
- If we want to switch between the server/database and also the customer name we just need to switch the parameters’ values
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
- Select DimSalesTerritory from Queries pane
- Add a row filter on “SalesTerritoryCountry” column
- Select “Parameter” for filter type then select “Countries” parameter from the drop down list then click OK
- As you see DimSalesTerritory is filtered for the rows that their SalesTerritoryCountry is equal to “United States”
- Now switching the parameter value will change the “Filter Row”
- Click the “Countries” parameter from the Queries pane then change Current Value to Canada
- Select DimSalesTerritory again and you’ll see that the is changed
- Parameterising Filter Rows is done!
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???
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.
Very good article! Thank you. I wonder though how one uses Query instead of List of values as Suggested values in parameter definition?
Hi gvg,
Thanks for your feedback.
I think this post answers your question: https://biinsight.com/power-bi-desktop-query-parameters-part-3-list-output/
Cheers
Hello,
The pictures doesn’t loaded, please correct the pictures link.
Hi Nikode.
Thanks for your feedback. As a matter of fact, all the images are loading without any issues.
Have you tried different browsers/devices?
Thanks.
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.
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
Hi,
I have an issue to get the data
1. I can able to connect from Power BI to SAP BW On Hana
2. I am trying to pull the data from SAP BW using Direct Query.
3. I have parameter to pull the data, the Preview of the data structure is not displaying, even I have chosen the option from the search button, I have selected both Technical Name & Preview Enabled, still I am not getting the Preview
4. Once I have filled the parameter details, then selected only required, there I am getting the Preview,
5. After preview, I have load button to load the data structure.
6. When I pull the Key figure for Display it shows as Blank.
7. The Source has data, When I use SAP GUI as RSRT T Code, I am getting the data.
8. I have checked the BeX Query for External Use the Check box for OLE DB Connection is checked
9. The Key Figures are not hidden.
I have checked the Option in the Power BI Desktop all needed Options are selected.
Any idea why key figures are not displaying when I load the data and structure from SAP BW to Power BI?
I am using SAP hana Direct query and want to pass parameter in my direct query like this.
SELECT * FROM “Schema1”.”FN_2019″();
I want Schema1 as parameter in my queries so when i change parameter value ; all queries show result acordingly.
Thanks,
Kul
Nice Article, but I have another scenarios.
I have a date slicer on desktop and i have created a date parameter and queries with @date parameter to find records on basis of parameter values and i have set current value of date parameter to ’10/22/2019′ and it is fetching records only one day records even i changed slicer value . I already load parameter to model and set slicer value to this parameter but no luck.
Hi Sushil,
Welcome to BIInsight.com.
Here is my reading from your explanation, you defined a date parameter in Power Query, you also have a slicer on your report and you expect that the date changes based on the value(s) you select in the slicer.
If that’s correct then I have to make clarify things a bit.
Power BI includes Power Query (M) layer, a Model layer and a Report layer.
– Power Query (M) is your data preparation layer (including query parameters)
– The Model on the other hand, keeps all relationships, measures, calculated columns and calculated tables
– The Report layer is where you visualise your data using different visuals available to you like Line Chart, Bar Chart, Slicers etc…
In data preparation layer you use Power Query (M) expression language and in the Model you use DAX.
Here is how the data flows between different layers in Power BI:
Power Query (M) => Model => Report
Making changes in an upper level do NOT propagate to the lower layers, therefore, when you change the values in a slicer it doesn’t make any changes in you Power Query.
Hopefully it is more clear now (if I correctly understood your question).
Cheers.
Thank you for the presentation. How can I use the Text Filter to update the created parameter value in Power BI and filter data on my visuals?
Hi Adeshina,
Welcome to BIInsight.com.
My understanding of your question is that you have a query parameter in Power Query, you also have a some visuals on your report and your expectation is that whenever you change the parameter’s value your report visuals reflect the change. Well, this is a normal behaviour of query parameters. But if you expect the query parameter to act like a slicer on your report, like how we use parameters in SSRS reports, then I have to clarify some concepts.
Power BI has 3 layers; Power Query (M) layer, a Model layer and a Report layer.
– Power Query (M) is your data preparation layer (including query parameters)
– The Model on the other hand, keeps all relationships, measures, calculated columns and calculated tables. This is your semantic layer.
– The Report layer is where you visualise your data using different visuals available to you like Line Chart, Bar Chart, Slicers etc…
In data preparation layer you use Power Query (M) expressions while in the Model you use DAX.
Here is how the data flows between different layers in Power BI:
Power Query (M) => Model => ReportMaking changes in a layer from the right, do NOT propagate to the left layers, therefore, when you change the values in a slicer it doesn’t make any changes in you Power Query.
Hopefully it is more clear now (if I correctly understood your question).
Cheers.
can you please help with this problem:
I want to be able to set a “Between Start Date and End Date” parameter so that it extracts data between two custom dates.
I have created two parameter tables; one in which I can type in the Start Date (tblStartDate), and the other in which I can type in the End Date (tblEndDate).
please help , thanks
Hi Umar,
Please refer to Scenario 2 explained in this post which shows how to filter rows by parameters which in this scenario the parameter is “Country”.
All you need to do is that after creating your parameters, you filter rows by Start Date and End Date.
Cheers.
I also need to change the WHERE clause in a SQL query using a parameter, but I need to dynamically calculate the EndDate parameter to be today’s date.
I’ve studied the scenarios, but still can’t figure it out?
Hi Hendrik,
Welcome to BIInsight.com.
Not too sure what you’d like to do, it would be great if you elaborate the scenario a bit more.
But speaking of SQL Server, you can use GETDATE() function in your WHERE clause.
If you’d like to handle this in Power Query then you can use DateTime.LocalNow() function.
If you explain your scenario a bit more I probably can help more.
Cheers.
Very nice explanation. However, can i know if it is possible to pass parameter of user’s input to Powershell script, taking consider the datasource is API?
Thank you in advance.
Thanks for this post, I followed the steps for scenario 1 (Parameterising a Data Source). We have a question, though: we are using this techinque to configure two different data sources in SQL Server, with same table schema and data, but different connection strings. One of them is used by the development team, located on a different network and with no access to production environment. We would like them to develop the report from Power BI Desktop using developlment connection parameter, and then, change the parameter to query production database and publsih the report. The problem comes when they want to save the report: since the connection to production fails, the changes cannot be applied, and they can never publish the report pointing to production environment.
Thanks in advanced!
Hi Silvia,
Welcome to BIInsight.com.
There are three approaches immediately come to mind.
1- The Dev team members can publish the reports to production environment while the reports still point to the Dev DB. Then a Power BI Admin changes the parameter from the dataset settings in Power BI Service. This is helpful since the developers can easily publish the reports. But the reports show Dev data till the Admins change the parameter(s) values
2- The developers must finish their works with the Dev data, switch the parameter(s) values to Prod, save the file without applying the changes. Close the file, then login to the Power BI Service and import the report directly from the service.
3- The Dev people are not allowed to publish to Prod environment. They do not even have viewer permission on the production Workspaces. They develop the reports, then hand them to PBI auditors/admins who have permission to publish the reports to Prod.
The latter is my preferred approach as it is much safer and more controlled. I suggest you have a look at my blog post about Version Control in a Power BI Governance framework: https://www.biinsight.com/power-bi-governance-good-practices-part-2-version-control-with-onedrive-teams-and-sharepoint-online/
Hope it helps.
Cheers
Thank you so much, Soheil, that was fast!
We are trying with option 1, we’ll see how that works with our dev team. In this case, we admins didn’t want to take care of the project, but if nothing else works we’ll opt for the third option.
Have a nice day, and thanks again!
Hi Soheil,
Very usefull summary of the topic!
Once I’ve created a few parameters, is there a way to change the order in which they appear in the “Enter Parameters” window?
And is there a way to hide parameters from the “Enter Parameters” window that are used to create functions in Power Query Editor? These parameters are filled dynamically with a value when the functions is being called during refresh runtime, so usually it makes no sense to enter values for them. Nevertheless, by default they appear in the “Enter Parameters” window.
Thank you very much!
Best regards,
Martin
Hi Martin,
Welcome to BIInsight.com.
Re. the order of the parameters, they show up in the same order they appear in the Manage Parameters window.
If you don’t need to enter the values, then you don’t need a parameter. Perhaps, you can achieve the goal in a query with disabled data load.
Hopefully that helps.
Cheers.
Thank you for the article. Can we somehow change the data source in a power BI file, using a Power Platform flow?
I have a Sharepoint site, where the files are located at different locations for different users. So the source would need to change dynamically, for whosoever is invoking the Power Platform flow.
I have my pbix ready, with parameters, where the source URL would be based on parameter. But I am not getting how to integrate that with a Power Platform FLow.
Basically. a user who invokes the flow, his username folder should be his source of data for his dashboard.
Any help would be appreciated.
Hi Ayushi,
Welcome to BIInsight.com.
I believe a little bit of background on Query Parameters and how the end-user interacts with them will help to unwrap your question more efficiently.
First of all, it is crucial to understand the different development layers available in Power BI Desktop and how the end-users interact with them.
The first layer, from a development perspective, is the data transformation (also known as the data preparation) layer.
In this layer, we use Power Query to connect to the data sources, mashup the data, and make it available for the next layer, the Data Model layer.
After we load the data into the Data Model, we build a Star Schema (which is optimised for reporting and analytical purposes). We create our measures, calculated columns, calculated tables etc etc.
When we are done with our Data Model, we start visualising the data. So the last layer of Power BI Desktop is the Data Visualisation layer.
After we built our report in Power BI Desktop, we publish the report into the Power BI Service where the end-user(s) access their data via reports or the Data Visualisation layer.
Here is the thing, the Query Parameters are created in the data transformation layer, which means the end-user can NOT access them or have any interactions with them.
So, now, let’s get back to your question/scenario.
We now know that the end-user cannot interact with the Query Parameters (unless we are using DirectQuery and the source system supports Dynamic M Query Parameters which I don’t think is suitable in your scenario), so we have to implement the requirement another way.
I suggest you get the data from all users files from SharePoint (if the file structure is the same) to consolidate the data in a table or multiple tables.
Then control the user’s access via RLS (Row Level Security) which guarantees that the right data is accessible by the right person.
I discussed all of the above points in much more detail in my book that you can purchase from various platforms.
I hope that helps.
Cheers.
Hi Soheil,
Thank you for sharing such insightful article!
It would be great to seek your guidance on following.
I want to implement incremental refresh. The data source is SAP BW. However the date field fetched is of Text type. The query folding breaks if the field converted to Date type.
I have searched a lot on the internet but couldn’t find a workaround.
Kindly guide
Thank you,
Mahesh
Hi Mahesh,
Welcome to BIInsight.com and thank you for your question.
I must admit that I never used SAP BW, but as far as I know, it is a semantic layer with multidimensional cube architecture.
When we create a data model in Power BI, we are indeed building a Semantic layer on top of our data sources, which in your case it is already a semantic layer itself.
What I am trying to say is that I would try to use Power BI as a reporting tool only when I connect to a semantic layer.
Doesn’t it make more sense if you can implement incremental refresh on SAS BW itself instead of Power BI?
That aside, let’s focus on your challenge.
I presume your query is in Import mode.
I have a question before moving forward, how do you know that your query is not actually folding?
Please note, the fact that the View Native Query in the Power Query designer is disabled, does not necessarily mean that your query is not folding back to the source.
But if you are looking at the Query Diagnostic tool and you are sure that the step is not folding then it’s a different story.
With that, let’s assume your query is not actually folding.
While some of your expressions in Power Query might not fold back to the source, the mashup engine might compensate and apply the filter locally, which means you still might be able to set your incremental refresh.
So, unfolded Power Query expressions do not always translate to unsuccessful incremental refresh implementation. I encourage you to read this article.
But, be careful about it, you will not get the best performance after all even if you are able to successfully implement the Incremental Refresh.
I hope that helps.
Cheers
Thank you Soheil, I will surely try the article out!
Regards,
Mahesh
Hi Soheil ,
Thank you for a wonderful article.
I’m doing a direct query relational model. Did some parameters and I can see them in the report view. But when the report is published, I cannot see the parameter options. Can you please help.
Thank in advance
Madhu
Hi Madhu,
Welcome to BIInsight.com and thank you for asking your questions here.
Look, I am unable to answer your question unless you provide some more information.
You need to answer the following questions:
If you already have done all the above, then it is very hard to find a reason for not being able to see your Dynamic M Parameter values on the Service side.
I hope that helps.
Cheers
Is it possible to make a parameter select all if the user chooses to leave it blank? Currently, if I don’t put something in the parameter field, it only returns data if the value is Null VS all .
Hi James,
Yes, it is possible, but the implementation varies on a case-by-case basis. Would you please explain your scenario in more detail?
I am having issue with Queries they are running very slow.
We are using direct Query access as well as. I want to know when user logs in. I want to pass that users profile information like his Organization Name and Role to fetch that data dynamically.
I have a shared database I want user to see their own data based on their profile information.
HI, I have a question. If my parameter is a text datatype, i will add “&CustomerName&” in the query. How would I add the parameter to the query if the parameter is decimal datatype?
Hi Lekshmi,
Welcome to biinsight.com, and thanks for your question.
If your parameter is of type number, then you can use Text.From(YOUR_PARAMETER_NAME) function.
So it would be something like this:
& Text.From(Number) &Hopefully that helps.