Power BI parameters are one of the most requested features in Power BI. Parameters are generally used in reports and dashboards to make them more dynamic. Creative report developers can use parameters for any purposes, allowing the same to fulfill many purposes. This saves development and maintenance efforts, and allows users consuming the reports the ability to view data in many different ways, from a single powerful, dynamic report!
Dynamic reports are important to both report developers and users, as we can greatly reduce the number of reports required. By using Power BI parameters, a single report can be used for multiple purposes. For instance, Power BI provides the following capabilities in the backend data modelling through the use of parameters.
• Changing the data source
• Modifying query filters/values
Changing the data source:
You can create a multivalued parameter per source system, so the same report can be used with Dev/QA/Prod server names or SharePoint locations. All the M queries can then be modified by pointing the “Source” property to respective server parameter.
The developer develops the reports on the Dev server. After publishing the reports to workspace in the Power BI service the reports can be pointed to QA or Production via the parameter. This way developers do not need to access production data during reports development and thus reduce the workload on production servers.
Modifying query filters/values:
This can be implemented when there is a use case of limiting the data set to certain date or value. Start and end date parameters can be created in the model. A custom filter can then be created in the table for the field for which the dates need to be filtered.
Example: Connection string parameters can be created and modified in Power BI as follows:
1. Connect to a data source and import the required table from the source system.
2. Create a parameter, name it “ConnectStr”, and provide multiple server names as per your environment.
3. Modify the M query of the table to point the source name to the parameter created.
4. Publish the report to the Power BI service from the Power BI desktop.
5. Once you publish the report to the service, go to dataset and click on the ellipsis to see the properties of the dataset.
6. In the dataset properties, click on parameters and type in the source server that the report should point to and hit apply.
a. Please note that the list of values defined in Power BI Desktop will not be listed here. The person publishing the report needs to enter the connection string manually.
b. This is a Power BI Service limitation.
Conclusion: the report can be pointed to the appropriate source system without the need to re-publish for each environment!
Limitation of parameters in PBI:
• Reports can only be published from Power BI desktop, other publishing processes, such as SharePoint/One Drive will not work.
• If you are using the RestAPI to publish your reports, you can manage parameters in the Power BI Service.
• Even though the parameters are multivalued in Power BI desktop, once you publish to the Power BI service, the value of the parameter needs to be typed in by the developer.
• Power BI Parameters will only work in import mode, irrespective of data source type.
• To modify the parameters in the service, the developer will need admin rights to the gateway on which the source system resides.
Power BI Parameters save time for report developers and users. And the above example shows how easy it can be to develop the dynamic reports that will get your users excited about their improved ability to understand their data.
Sarada Karlaputi is a Senior BI Consultant at DesignMind in San Francisco. She has been delivering powerful, complex Power BI, Analysis Services, and other database solutions to many of DesignMind’s key clients. You can learn more about editing parameter settings in the Power BI service here. And you can read more articles by the DesignMind Power BI team here.