Microsoft Power BI Report Builder is a powerful tool for creating paginated reports. One common challenge developers face is passing multiselect parameters from paginated report to Power BI semantic model DAX queries. This functionality is crucial for creating dynamic, user-friendly reports that can filter data based on multiple selections.
In this blogpost, we’ll walk through the solution for multiselect parameters issue in Power BI report builder for DAX queries
Understanding the Problem
I have created a Power BI paginated report which shows daily patients visits for a hospital for each department. It is connected to a Power BI semantic model. Report has a single parameter for departments with multiselect option checked.
I have two datasets in the report. Departments dataset is used for the values of department parameter. DailyPatients datasets is the main dataset in my report.
DailyPatients dataset gets data from Microsoft Fabric semantic model using a DAX query and passes department as filter.
If I execute this report for a single value of department, then report shows data. However, when I select multiple values it does not return any data.
Root Cause
Power BI report builder take multiple selected values as a single string. In previous example when I selected multiple of departments (Pediatrics and Orthopedics) it saved it as “Pediatrics, Orthopedics”. It compared this string with departments in my dataset. As there was no such value of department so DAX query returned no rows.
Step by Step Solution
If we split the string using split function or custom code, it would not work as it will return an object as result. Instead will use PATHCONTAINS DAX function to handle this.
First I will modify the department parameter expression used for DailyPatients dataset. I will add join function with a pipe sign as second argument.
Next I will simplify my DAX and use PATHCONTAINS function.
Once I have done these two steps, I rerun the paginated report and it returned data for multiple parameter values.
If your want to learn about Power BI Report Builder, start with this easy tutorial on How to Download and Install Microsoft Power BI Paginated Report Builder