Forum Discussion
Paginated Report - multiple parameter via DAX query
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
Ahhhh...the solution was pretty easy. Putting it out here, in case, anyone runs into the same issue.
=Join(Parameters!GrowerGroup.Value,"|")
Thanks,
RK
12 Replies
Query Designer is a bug and should be removed/commented out
Vote to have QD removed from the m$ code base.
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/
Ahhhh...the solution was pretty easy. Putting it out here, in case, anyone runs into the same issue.
=Join(Parameters!GrowerGroup.Value,"|")
Thanks,
RK
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!
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
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
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,
