Multiple Value Parameters in Paginated Reports – With Power Query

Back in August 2024, I wrote an article on Multiple Value Parameters in Paginated Reports, focusing on embracing the DataOps principle of Simplicity.

Simplicity: We believe that continuous attention to technical excellence and good design enhances agility; likewise simplicity–the art of maximizing the amount of work not done–is essential.

Over the years, I’ve spent hundreds of hours training Power BI users on how to create DAX queries for semantic models to be used in Paginated Reports. Quite frankly, I’ve always found the integration between Paginated Reports and the Power BI Semantic Model to be cumbersome. Moreover, introducing users to RSCustomDaxFilter to handle the translation of multiple-value parameters in Paginated Reports often elicited the response:

“Why does Microsoft make this so hard?”

I never had a great answer, and I personally struggled with RSCustomDaxFilter for two main reasons:

1. Hard to Debug - RSCustomDaxFilter is only available in Paginated Report Builder. If you want to test it in DAX Studio or DAX Query View, you have to rewrite it using FILTER/VALUES functions. This makes troubleshooting inconvenient.

2. Syntax Errors - Column references use a different syntax. I can’t count how many times I’ve mistakenly used the DAX format ([table][column]) instead of the correct Paginated format ([table].[column]) and ended up wasting time on what turned out to be a small syntax issue.

Here’s a quick example demonstrating that difference (notice the ‘.’ for referencing Date ID):

VAR _FilterX = RSCustomDaxFilter(@ArrayOfValues,
EqualToCondition, [DateDim].[DateID], Int64)

EVALUATE SUMMARIZECOLUMNS(DateDim[DateID], DateDim[Date],
_FilterX)

So how do we make this simpler?

For many of my clients, the simplicity breakthrough came when Power Query for Paginated Reports became available. This feature allows you to use the same Power Query Editor found in Power BI Desktop directly within the Paginated Report Builder application.

Now, you can connect to dataflows, the Power BI Semantic Model, SQL Server, and more just like you would in Power BI Desktop. More importantly, you don’t need to know RSCustomDaxFilter, nor do you have to write any DAX code to build Paginated Reports.

Figure 1 - Life simplified with this Get data (Power Query) button.

I still meet colleagues who haven’t upgraded their Paginated Report Builder, and I highly recommend doing so. This new Power Query feature significantly simplifies the Paginated Report Builder experience.

Multiple Value Parameters

So, how do you use multiple-value parameters with the Power Query feature in Paginated Reports? Let me walk you through it.

Scenario

Let’s say you have a Projects table from a SQL Database or Power BI Dataflow, and you want users to filter on multiple projects to view their statuses, such as “In Progress”, when interacting with the report.

To set up multiple-value parameters, you’ll need:

• A “Projects” dataset to populate the filter dropdown.

• A “Project Statuses” dataset to display filtered results in the report.

In Figure 2, I’ve loaded a table into Power Query as an example:

Figure 2 - Example of Projects to filter

Now to setup for multiple value filtering you need to do the following:

Steps to Enable Multiple-Value Filtering

1. Create a Report Parameter

   • Name it something like ReportFilter.

   • Enable the "Allow multiple values" setting.

   Figure 3 – Create the ReportFilter parameter

2. Set Available Values

   • Use the Project field from the Projects dataset.

   Figure 4 - Set Available Values

3. Create an Internal Parameter

   • Create a second parameter (e.g., ReportFilterConcat).

   • Set its visibility to Internal.

   Figure 5 - Create Internal Parameter

• Set the default value to:

  =Join(Parameters!ReportFilter.Value, ",")
  

  This joins the selected values into a comma-delimited string.

  Figure 6 - Set Default Value

1. Reference the Concatenated Parameter in the Dataset

   • In the Project Statuses dataset, add a new parameter named ReportFilterConcatPQ.

   • Set its value to the ReportFilterConcat internal parameter.

   Figure 7 - Reference ReportFilterConcat within Project Statuses dataset

2. Edit the Power Query ("Mashup") for the Project Statuses Dataset

   • Click Edit Mashup.

   Figure 8 - Edit mashup...

3. Create a Power Query Parameter

   • Inside the Power Query editor, create a parameter named exactly ReportFilterConcatPQ.

   • This links your report parameter to the query.

   Figure 9 - Create parameter in Power Query

4. Split the Values in Power Query

   • Create a new blank query and use:

     Text.Split(ReportFilterConcatPQ, ",")
     

   • Name the query something like "Report Filter List".

   Figure 10 - Create Report Filter List

5. Filter the Dataset

   • Use the “Report Filter List” query to filter the Project column in the dataset.

   Figure 11 - Filter Projects by Report Filter List

6. Test the Report

   • When you run the Paginated Report, you should now be able to select multiple projects from the dropdown, and Power Query will filter the results accordingly.

   Figure 12 - Test Report

Sample File

I’ve updated my repository paginated-reports-multivalue-example with a sample file: paginated-reports-multivalue-example-power-query.rdl

Feel free to download and explore the implementation.

As always, let me know your thoughts on LinkedIn or Twitter/X on the approach and ways to improve it. If I can simplify this approach further, create a pull request on the repository and I’d be happy to review.