cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ritesh_Air
Post Patron
Post Patron

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

1 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

View solution in original post

4 REPLIES 4
Jon-Heide
Microsoft
Microsoft

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

View solution in original post

@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

@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

Ritesh_Air_0-1618762230847.png

 

Option 2: replacing TREATAS as PATHCONTAINS...

 

I get error saying it's not correct.

 

 

What am I doing wrong?

 

Thanks for your help!

-RK

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors