Multi Valued Parameter in Report Builder with a a DAX Query

[Hoping]

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"} ) )

[Hoping]

 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.

[OwenAuger]

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.

[OwenAuger · Accepted solution]

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.

See also this post from Chris Webb.

I've attached my test files in case they're of any help.

In the example I set up, using Query Designer, I ended up with this query which uses the RSCustomerDaxFilter syntax:

EVALUATE SUMMARIZECOLUMNS('SampleTable'[SampleColumn], RSCustomDaxFilter(@SampleTableSampleColumn,EqualToCondition,[SampleTable].[SampleColumn],String))

This is translated into a proper DAX query when the report is run, e.g.

EVALUATE
SUMMARIZECOLUMNS (
    'SampleTable'[SampleColumn],
    FILTER (
        VALUES ( 'SampleTable'[SampleColumn] ),
        ( 'SampleTable'[SampleColumn] IN { "B", "C", "D" } )
    )
)

For testing, the Data Source "Local_PBIX" is an Analysis Services connection to a local PBIX, which you can adjust by changing the Connection String.

Does that help at all?

Regards