Paginated Report - multiple parameter via DAX query

[Ritesh_Air]

Hello, I am using DAX generated from PowerBI report (using Performance Analyzer) and using it in Paginated report. When the parameter has multiple values, the query doesn't understand it and returns blank. I see generated DAX query has parameter like that.

Query generated by Power BI Performance Analyzer :

VAR __DS0FilterTable2 = 
    TREATAS({@GrowerGroup}, 'Enrolled Growers'[Program_Group_Name])

How do I change this TREATAS to accommodate multiple values something like what Query designer does ? I tried to use RSCustomDAXFilter in above query but didn't understand how do I replace TREATAS with RSCustomDAXFilter

Query generated by Query Designer :

EVALUATE SUMMARIZECOLUMNS
('Enrolled Growers'[Program_Group_Name], 'POS'[Points],
RSCustomDaxFilter(@GrowerGroup,EqualToCondition,
[Enrolled Growers].[Program_Group_Name],String))

Thanks,
-RK

[garythomannCoGC]

Query Designer is a bug and should be removed/commented out 

Vote to have QD removed from the m$ code base.

[loicr]

Hi RK,

It seems that you were able to pass a parameter in a query generated from Power BI.

Could you explain how you did? When I'm trying, I get some errors message and it seems it was not a problem for you.

Best,

[Jon-Heide]

Hey Ritesh, the RSCustomDaxFilter when executed does a literal string replacement with FILTER(VALUES(...). So you could try getting your query from the perf analyzer with a single hardcoded FILTER() in the query. Then, just replace that filter with RSCustomDaxFilrer(...). Take a look at Chris Webb's blog for a good rundown on what it does. 

https://blog.crossjoin.co.uk/2019/11/03/power-bi-report-builder-and-rscustomdaxfilter/

[Ritesh_Air · Accepted solution]

Ahhhh...the solution was pretty easy. Putting it out here, in case, anyone runs into the same issue.

=Join(Parameters!GrowerGroup.Value,"|")

Thanks,

RK

[mimara79]

Hi @Ritesh_Air , 

Sorry but my english is not very good. I have the same doubt as @NielsDW (06-25-2021 10:14 AM). Can you please elaborate where did you fill in this "Join(Parameters!GrowerGroup.value, "|").

I do not know if it is correct here where I have put it as seen in the screenshot below (in my case the parameter is called "Viajante"), but it does not work for me either and I have also tried to put =Join(Parameters!Viajante.Value,","). When I select a single value, it returns the correct list. When I select multiple values, a blank list is returned. 

I also have doubts if I should use the TREATAS function or some other (screenshot 2).

What am I doing wrong?. I hope you or someone can help me.

Thanks in advance!

[Ritesh_Air]

In dsMain:

See if it works. @mimara79 @NielsDW 

[mimara79]

@Ritesh_Air thank you very much for answering and for the captures.

Perfect, now it works correctly!

Now I am trying to configure the filter so that the value "Select all" appears by default but it does not work with what I am putting.
I know that if I put all the values ​​that exist in the "Viajante" field as seen in the screenshot below it works correctly but there are many values ​​and I would like to know if there is any other way to do it and not have to put all the values.

I don't know if you or someone can help me with this.
Thank you very much again.

[mimara79]

Hi, I already found the solution to my previous post about the default "Select All".

I put the screenshots below
in case there is someone who has the same question and can help you.

Maybe there is a better way to do it but I don't know.

[NielsDW]

Hi Ritesh_Air, 

Can you please elaborate where did you fill in this "Join(Parameters!GrowerGroup.value, "|").

I am running into the same issue regarding filtering parameter values in my table. When I select a single value, it returns the correct list. When I select multiple values, a blank list is returned. 

Thanks in advance!

[Ritesh_Air]

@Jon-Heide 

One more update:

If I change my variable to this:

  VAR __DS0FilterTable2 = 
    FILTER(
      VALUES( 'Enrolled Growers'[Program_Group_Name] ),
      PATHCONTAINS(@GrowerGroup, 'Enrolled Growers'[Program_Group_Name] )
    )

I don't get any syntax error and query runs fine. If I choose 1 value from the drop down list, I get correct result, but if I choose multiple then I get no data. So somehow, it doesn't recognize multiple values, even after using PATHCONTAINS functions. I am stumped. 😞

Thanks,

-RK

[Ritesh_Air]

@Jon-Heide Thanks for your input.

Can you please explain further? I don't see it coming as FILTER (VALUES.....) while I put single filter. How would I force it to generate FILTER (VALUES....) code? I get TREATAS code....

I put a single filter in the filter pan and I get this:

// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({"172 Garing"}, 'Enrolled Growers'[Program_Group_Name])

If I replace the line as this:

// DAX Query
DEFINE
  VAR __DS0FilterTable = 
   RSCustomDaxFilter(@GrowerGroup,EqualToCondition,
   [Enrolled Growers].[Program_Group_Name],String))

It didn't work. Now I tried Chris's blog and tried this: Using PATHCONTAINS.

  VAR __DS0FilterTable2 = 
 filter(
Enrolled Growers
, pathcontains(@GrowerGroup, Enrolled Growers[Program_Group_Name])
)

Option # 1: Replacing TREATAS as RXCUSTOMFILTER

Option 2: replacing TREATAS as PATHCONTAINS...

I get error saying it's not correct.

What am I doing wrong?

Thanks for your help!

-RK