In this article, I am going to explain how we can create data source parameters in Power BI.
If you have different environments like Development, QA (UAT), and Production, and you want to check your reports on those environments without spending too much time changing the data sources.
So, that time you need to create a data source parameter to toggle between those data sources.
In this scenario, the data is coming from the Power Bi Dataflows.
So, there are three workspaces in that dataflow is present:
- QA (UAT)
And dataflow is
- Flat File
- Fact tables
Let’s come to the point. How to create Data Source Parameters?
Step 1. Open your report on which you want to set the data source parameter. Then click on the transform data then look for manage parameters and click on it. The pop-up window will open just click on the New which is in blue just below the title of the manage parameter.
Step 2. Create parameters for workspace and dataflows like dimension, flat file, and fact tables. While creating those parameters add names like “WorkspaceName, DataflowNameDim, DataflowNameFact, DataflowNameFlatFiles” in the name field.
It will give your user(client) an easy understanding.
Unleash the Potential of Power Platform With a Center of Excellence
Business innovation often comes from within. Discover how to empower innovation from non-traditional developers with the Microsoft Power Platform.
After that, you need to add a small description just like “the name of the workspace the dataflow is in, name of the dataflow for dim tables, name of the dataflow for fact tables” like this.
Then select type, by default it is “Any”. Generally, we use the IDs for creating data source parameters but in this scenario, our IDs would be changing in the future, so use the workspace name and dataflow name in the place of Ids.
So, for that purpose, I have selected the type “Text”.
Then put the workspace name for the WorkspaceName parameter in the current value field and the dataflow name for DataflowName parameters. Then click on OK.
Step 3. Then you can see those parameters in the queries section.
Step 4. The next step is what you going to do is select a table from queries and go to the advanced editor. You will see the code for the source, by default it is ID.
Step 5. So, what are going to do is change the workspaceId to workspaceName and add the WorkspaceName parameter that we have created. Same for the dataflowId change it to dataflowName and add DataflowName parameter here.
It is important to do because we used the text Type. Without doing this our parameter doesn’t work.
Do this process for all the tables that you have used in your report and change the workspace and dataflow IDs to Name by using advanced editor. Then click on DONE.
Yes, you have set your parameter successfully, just click on close and apply and save the report..
Step 6. For testing you can try to change the workspace, for that, you just go to transform data click on the arrow and you will see the edit parameter, click on that and the pop-up window will open. You just simply change your workspace name to change your data source. If it is UAT and you want to see the Production data you just need to change the workspace name UAT to Production. It will turn your whole report into production data in one click and the “i” button shows the details about the parameter that we have added in the description box.
You can also change the data source in Power BI Service.
Note: The table names and the column names in the dataflow of different workspaces should be the same to toggle it from One workspace to another workspace.