Multiple Value Parameters in Paginated Reports

Paginated Reports offer us the ability to generate near pixel-perfect exports of our reports in PowerPoint, PDF, etc. Often on my teams, we provide a button in our regular Power BI Report to export contents of the underlying semantic model that is nicely formatted to help our clients prepare for briefings or hold meetings. Over the years, I have learned that you should not assume that within different working environments a laptop and connectivity is always there, so exports still have a real use case. The image below illustrates the design pattern I’ve used.

Figure 1 - A Design Pattern for having thin reports export in a pix-perfect format using Paginated Reports.

In order to support this design pattern, my teams leverage query string parameters so we can pass the current filters from the report over to the Paginated Report. When we have to send multiple values for the same filter (say Fiscal Year) it typically looks like this: “rp:FY=2024&rp:FY=2023”. Notice that you must repeat the parameter name to pass multiple values to the same parameter.

The Problem

This format can be problematic because of the URL limit of 2,083 characters placed by Microsoft Edge. The repetition of the parameter name over and over for multiple filter values can quickly reach that limit. In DataOps, we try to embrace the concept 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.

With that in mind, wouldn’t it be simpler to send “rp:FY= 2023,2024”? … It would except Paginated Reports have a hard time converting that into a format that can be used by the DAX Query in the Paginated Report.

Any multi-value parameter in Paginated Reports that are used to query a semantic model (with DAX) require the mysterious RSCustomDaxFilter. Thanks to Chris Webb, I came across this function early in my Paginated Report journey. Here is an example of the function:

Figure 2 - My function definition for RSCustomDAXFilter

Under the hood, Paginated Reports will translate this function into the appropriate DAX (think FILTER function) so you can work with multi-value parameters. The problem is, the first argument must be an Array (multi-value), so a comma-delimited string like “2023,2024” won’t work (ugh). This feels like a classic square-peg, round-hole issue where two Microsoft products struggle to integrate, and the community must endeavor to look for workarounds to make our customers happy.

Solution

I found a solution with two options that converts a comma-delimited string from a parameter into the array we need. One option is the DAX option, and the other is the Code option. Why two options? Well, the DAX option allows you to manipulate the comma-delimited string and add additional logic using data from the model. For example, maybe you want to convert a string like “PortfolioA,PortfolioB” into a set of IDs that represent the project in each of those portfolios. This allows you to create a set of mnemonics that reduce the URL size and can then be expanded to a range of numbers to query the model. Let’s look at the DAX Option a little deeper.

DAX Option

Figure 3 - DAX Option flow

Figure 3 provides an illustration. At a high-level, here are the steps:

1. We take the comma-delimited string, split those values into an array, and store those results as another parameter. The key is to leverage the same semantic model which we retrieve data from to also convert our string to an array. Here is the DAX that does the conversion.

-- Define the input variable containing a comma-delimited string  
VAR _X = @CommaDelimitedStr  
  
-- Replace commas with pipe characters to prepare for PATHITEM usage  
VAR commaList = SUBSTITUTE ( _X, ",", "|" )  
  
-- Calculate the number of items in the comma-delimited string  
VAR commaListLength = LEN ( _X ) - LEN ( SUBSTITUTE ( _X, ",", "" ) ) + 1  
  
-- Generate a table with a row for each item in the comma-delimited string  
VAR tempTable =  
    ADDCOLUMNS (  
        GENERATESERIES ( 1, commaListLength ),  -- Create a series from 1 to the number of items  
        "mylist", PATHITEM ( commaList, [Value] )  -- Extract each item based on the series index  
    )  
  
-- Select only the "mylist" column from the temporary table  
VAR arrayOfItems = SELECTCOLUMNS ( tempTable, "list", [mylist] )  
  
-- Output the final table with the list of items  
EVALUATE  arrayOfItems

2. We receive the array of values from Step 1 and use it to set the default values of an internal parameter. Therefore, in this solution we have a two parameters, a public one that was supplied as a comma-delimited string, and another internal parameter that stores the comma-delimited values in an array (multi-value parameter).
3. The internal parameter is then used as an argument in the RSCustomDAXFilter to issue the DAX query to the semantic model.
4. The results of the DAX query are returned to the paginated report. Here those results are used for building charts, setting up tables, in other words, the typical tasks you would do in building a Paginated Report.

Code Option

The Code option is more straightforward and leverages custom code in a Paginated Report. This is a good option when we just need to convert the comma-delimited values and do not need additional business logic from the model. You could also extend this function to do additional steps if need be. With this option, you do the following:

1. Create a function ConvertToArray that converts the comma-delimited string to an array. Figure 4 - Example of custom code in Paginated Report
2. Then within the Parameter window of the Dataset properties, create a new parameter by converting the comma-delimited string parameter into an array in the dataset using the custom code you just created. Figure 5 - Using the custom code to convert the comma-delimited string to an array
3. Issue the DAX query using the RSCustomDAX Filter. Figure 6 - Example of using the RSCustomDAXFilter function in DAX.
4. The results of the DAX query are returned to the Paginated Report. Here those results are used for building charts, setting up tables… you know, Paginated Report tasks.

Give it a Try

With this workaround, my teams have been able to avoid the URL length limit while maintaining support for the multi-value filters. The exports to PowerPoint and Excel are saved, hurray!

I have shared a template at this link if you’re interested in trying it out.

This workaround also works nicely with the Paginated Report Power BI Visual (see Figure 7) since it’s also difficult to send an array of values in the Field Well. Instead, you can create a comma-delimited list of values using a quick measure and pass that into the visual.

Figure 7 - Example of this solution with a Paginated Report visual in Power BI Desktop

As always, let me know your thoughts on LinkedIn or Twitter/X on the approach and ways to improve it.