Multi-Parameterized Report in Power BI Report Builder

Question

Greetings Community,

I am currently working on creating a multi-parameterized report using Power BI Report Builder. The primary objective is to establish default "Select All" settings for the following parameters: Store Name, User Name, Doctor Name, Hospital ID, Drug Name, Sale Type, Start Date, and End Date.

Specific Requirements:

All parameters should default to "Select All" for a comprehensive view of the data.

Users should have the flexibility to make specific selections dynamically.

For instance, if a user chooses to filter by Doctor Name, they can simply click on the dropdown for the Doctor Name parameter and select a specific doctor. The report should then adjust dynamically, updating the display for the chosen doctor while keeping other parameters as "Select All."

Similarly, if a user wishes to filter by Store Name, they can pick a specific store, and the report should adapt dynamically to display data relevant to the selected store. The other parameters should retain their default "Select All" behavior.

Technical Context:

The underlying data query is composed in DAX.

If anyone has encountered a similar requirement or has a solution for this scenario, kindly share your insights.

I appreciate any guidance or solutions that the community can provide.

 

Thank you,

Chandrasekhar

 

Answer 1

Based on your requirements, you need to create a report parameter for each of the parameters you mentioned: Store Name, User Name, Doctor Name, Hospital ID, Drug Name, Sale Type, Start Date, and End Date. You also need to set the default value for each parameter to “Select All” and enable the option to allow multiple values. This way, the user can select one or more values for each parameter and the report will filter the data accordingly.

To create a report parameter, you can follow these steps¹:

• In Power BI Report Builder, open the report that you want to add the parameter to.
• Right-click the report dataset, and then click Dataset Properties.
• Add a variable to the dataset query by either editing the query text in the Query box, or by adding a filter by using the query designer. For example, if you want to filter by Store Name, you can use something like this:

SELECT * FROM Sales WHERE StoreName IN (@StoreName)

• A dataset parameter for an embedded dataset or a shared dataset is created automatically for the query variable. A report parameter is created automatically for the dataset parameter.
• In the Report Data pane, expand the Parameters node, right-click the report parameter that was automatically created for the dataset parameter, and then click Parameter Properties.
• In the General tab, select Allow multiple values to allow the user to select more than one value for the parameter.
• In the Default values tab, select Specify values and add a value with the label “Select All” and the value “*”. This will make the parameter default to “Select All” and match all values in the dataset.
• (Optionally) In the Available values tab, specify a list of available values to display to the user. An available values list limits the choices a user can make to only valid values for the parameter. For multiple values, the top of the list begins with a Select All feature so the user can select or clear all values with a single click. If you choose to get the available values for the report parameter from a dataset query, be sure to select a dataset that does not contain the query variable that is associated with the same report parameter.

You need to repeat these steps for each of the parameters you want to add to your report. You can also use expressions to customize the behavior of your parameters, such as hiding or showing them based on other parameter values².

I hope this helps you with your report. If you have any further questions, please feel free to ask. 😊

¹: Add, Change, or Delete a Report Parameter (Power BI Report Builder) ²: Use Expressions in Report Builder to Vary Report Presentation (Power BI Report Builder)