topic Re: Multi Valued Parameter in Report Builder with a a DAX Query in DAX Commands and Tips

Multi Valued Parameter in Report Builder with a a DAX Query

Hoping — Sun, 25 Aug 2024 00:10:55 GMT

I am creating a paginated report from a report from a Power BI model.

I am trying to pass multiple valued parameter to the DAX query.

I see that a lot of blogs say it is not possible. I want to understand why exactly.

Lets I have a parameter "@paramet" and it can take multiple values "A" and "B

If power builder is returning "A, B" to the DAX query, is there a way to convert it into a table (ParametTable) with single column of values {"A", "B"} so that it can be used as follow:

VAR FilterMultipleValues = 'SampleTable'[SampleColumn] IN (ParametTable)

If I hard code the parameter values as shown below in DAX Query View of Power BI Desktop it works.

VAR FilterMultipleValues = FILTER(KEEPFILTERS(VALUES('SampleTable'[SampleColumn])),

'SampleTable'[SampleColumn] IN ( {"A", "B"} ) )

Re: Multi Valued Parameter in Report Builder with a a DAX Query

OwenAuger — Sun, 25 Aug 2024 13:54:51 GMT

Hi Hoping

It is certainly possible to have multiple-valued parameters in Paginated Report passed to the DAX query for a Dataset.

It's a while since I'd set one up, but I was able to do so just now following this article which covers some examples of query-level parameters (among other things). See the section near to the top titled Parameters and filters that are applied at the Query Level.

Re: Multi Valued Parameter in Report Builder with a a DAX Query

Hoping — Tue, 27 Aug 2024 22:09:19 GMT

Thank You OwenAuger . I did not know about RSCustomDaxFilter. Will read more about it. Length of DAX query does seem like something to be worried about. Meanwhile, I used the technique given in this Chris Webb's Blog which uses PATHCONTAINS DAX function on a concatenated string of parameters. This worked well for me.

But why all the work arounds? Is it because DAX cannot handle string of dynamic lengths ? I am keen to understand why.

Re: Multi Valued Parameter in Report Builder with a a DAX Query

OwenAuger — Tue, 27 Aug 2024 23:47:09 GMT

No problem 🙂
On RSCustomDaxFilter, apparently an update in August 2020 addressed some issues with query length due to large numbers of parameters, but I don't know specifically what was changed.

The fundamental issue is that the the Paginated Report list of parameter values needs to be translated into a single-column table within the DAX query.

In Chris Webb's article you referred to, this single-column table is created within the DAX query using PATH functions and GENERATE (after having created a pipe-delimited list with the SSRS Join function).
With RSCustomDaxFilter, the DAX is auto-generated and appears as a table of literal values, such as

{ "B", "C", "D" }

I'm assuming the RSCustomDaxFilter method is the preferred method given the August 2020 update, and the fact that PATH functions would run into trouble if any text values containing the "|" character.

However, it would be interesting to test with very long lists of parameter values.