Managing Parameters in Power BI Report Builder with Published Data Sources

As businesses shift towards cloud-based solutions in data analytics and business intelligence, many forward-thinking organizations seek to leverage the latest and greatest of technologies to enhance their reporting capabilities. With the shift towards cloud-based solutions, migrating SQL Server Reporting Services (SSRS) reports to the Power BI service has become a popular choice for organizations looking to unlock the full potential of their data.

Transitioning from SSRS to Power BI involves understanding the nuances of parameters, which differ between the two platforms. This article examines the pivotal role of parameters in this migration and offers insights on optimizing their use in Power BI Report Builder.

Prerequisites

Below are the software requirements for migrating Paginated report to Power BI Service:

Power BI Service Subscription:

You have a Power BI Pro or Premium Per User license.
You have write access to the workspace.

Step 1

Open Power BI Desktop and then click on Get Data to connect to SQL Server. After passing the below values. Click on OK. Note here we just used the AdventureWorks2022 database present in SQL Server. You can download it here.

Step 2

Click on Transform Data after passing the credentials and make a transformation if required. Here two datasets are added one is the Products (main query) and Products (for parameters use). Then click on Close & Apply.

Step 3

Publish the Products.pbix file to Power BI Service on My workspace.

Step 4

Open the Power BI Report Builder, in the Report Data pane, right-click on Data Sources and click on Add Power BI Dataset Connection. It will redirect you to the Power BI service sign-on page where you can sign in.

Step 5

Now you can select Shared with me, where you can see published data sources on Power BI service. Select the respective one (Products in my case).

Step 6

Now your data source is added, and you can create a Dataset based on that by right-clicking on Dataset and then Add Dataset.

Step 7

Provide the Name of the Dataset and select the data source that you have created previously, then click on Query Designer.

Step 8

Now you have to drag the columns from the left side to the center area, and there you can run the query to see the data.

Step 9

Click on the Design mode on the top icons that shows the relative DAX query for all columns. Now, we have to modify the DAX query for parameters to be created and filter the relative dataset.

There are two types of parameters in Power BI report builder:

Single value parameter
Multi-value parameter

Use Single Value Parameter When:

You need users to filter the entire report based on a single choice.
The parameter selection directly affects the main content of the reports.
Users need to focus on a specific category, time period, or entity.

Use Multi Value Parameter When:

You want users to select and view data for multiple options simultaneously.
The report needs to display data for a combination of selected criteria.
Users need to analyze and compare data across different categories, entities, or dimensions.

Based on the parameter selection, the DAX queries can be modified so that parameters can work seamlessly. In each case, there is a different DAX that is added to filter the query dataset.

For Single Value Parameter, the DAX can be modified as

				
					FILTER(VALUES('Dataset Name'[Column Name]),('Dataset Name'[Column Name] IN {@Parameter Name}))

Here @ProductId is a parameter that needs to be created.

For Multi Value Parameter, the DAX can be modified as

				
					FILTER(VALUES('Dataset Name'[Column Name]),PATHCONTAINS(@ParameterName,'Dataset Name'[Column Name]))

Here @ProductId is a parameter that needs to be created.

Step 10

After that, click on the Query Parameters iconic top to create a ProductId parameter. In Dimension, you need to select the dataset, and in Hierarchy, you need to select the appropriate column name (in our case it is ProductID). Then click OK two times.

Step 11

Now for Single Value Parameter, click on Parameters to assign the parameter value.

For Multi Value Parameter, click on Parameters to assign the parameter value as an expression by clicking ƒx and update it with

				
					=Join(Parameters!ParameterName.Value,"|")

Step 12

For Single Value Parameter, don’t check the Allow multiple value checkbox in the General tab and for Multi Value Parameter check the Allow multiple value checkbox.

Now, double click on Parameters to open its properties to change the Available value to ProductIdParam dataset which is having distinct Product id. Click on OK.

Step 13

Run the report to see the results.

For Single Value Parameter

For Multi Value Parameter

Conclusion

Parameters are indispensable tools that elevate the effectiveness and user experience of Paginated reports. Embracing parameter-driven reporting empowers organizations to deliver personalized, interactive, and data-rich reports, facilitating better decision-making and driving business growth. As organizations continue to harness the power of data, leveraging parameters in Paginated reports will remain a cornerstone of success, transforming data into actionable insights that drive excellence across all aspects of an organization’s operations.

Need help with the Microsoft Power Platform? phData can help! Our experts thrive at guiding leading businesses toward uncovering actionable insights with Power BI. Contact us today to learn how we can help.