Forum Discussion

Hoping's avatar
Hoping
Icon for Helper III rankHelper III
2 years ago
Solved

Multi Valued Parameter in Report Builder with a a DAX Query

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 wan...
"DAX help"
  • OwenAuger's avatar
    OwenAuger
    2 years ago

    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

Hoping's avatar
Hoping
Icon for Helper III rankHelper III
2 years ago

 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's avatar
OwenAuger
Icon for Super User rankSuper User
2 years ago

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.