Power Bi Report Builder - DAX Query from Performance analyzer multiple parameter values issue

[JWick1969]

Hi All,

I have created a report using PowerBI Report builder and the dataset query is coming from Performance Analyzer.

I define a Year and Month parameter but if I choose a multiple value on year or month i'm getting an error or no data displayed on the report. If i choose single value on the parameter the report is working. 

Another thing, if i choose multiple value on year parameter, i'm also getting an error.

cannot covert value <pii>2019|2020</pii> of type tp type integer while processing column <pii>[static column]->datetable[Received Year]</pii>

Parameter: set expression value

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

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

Below is the DAX query.

// DAX Query
DEFINE

VAR __DS0FilterTable =
TREATAS({@Year}, 'DateTable'[Received Year])

VAR __DS0FilterTable1 =
TREATAS({@Month}, 'DateTable'[MonthName])

VAR __DS0FilterTable2 =
TREATAS({"Phone"}, 'CSST'[Product Group])

VAR __DS0FilterTable3 =
TREATAS({"AER"}, 'CCST'[Category])

EVALUATE
TOPN(
501,
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
'CSST'[Category], "IsGrandTotalRowTotal",
'CSST'[Client], "IsDM0Total",

I believe the parameters are just not working correctly or the DAX query, likely, something wrong I did, but I have no idea how to fix this. I have tried innumerous things! Please help! 

Thank you!

[d_gosbell · Accepted solution]

So the basic order of operations for building a query as an expression is as follows:

1) you create a new data set and link it to a connection.

2) then you click the fx button to open the expression editor

3) then you start the expression with an equals and an opening double quote character eg.  =" 
4) then I normally open a text editor paste my query in there and do a global search for any double quote characters " and replace them with 2 double quote characters ""

5) then you paste in your query and at the end you add a closing double quote character "

="DEFINE
  VAR __DS0FilterTable = 
    TREATAS({2021}, 'DateTable'[Received])

  VAR __DS0FilterTable2 = 
    TREATAS({""November""}, 'CCST'[MonthName])

  VAR __DS0FilterTable3 = 
    TREATAS({""LEGACY""}, 'CCST'[Model])

  VAR __DS0FilterTable4 = 
    TREATAS({""DIY""}, 'CCST'[Type])

  VAR __DS0FilterTable5 = 
    TREATAS({""Normal""}, 'CCST'[Status])

  VAR __DS0FilterTable6 = 
    TREATAS({""OTHERS""}, 'CCST'[Category])

EVALUATE
  TOPN(
    501,
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL(
        'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Year], ""IsGrandTotalRowTotal"",
        ROLLUPGROUP(
          'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Month],
          'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[MonthNo]
        ), ""IsDM0Total""
      ),
      __DS0FilterTable,
      __DS0FilterTable2,
      __DS0FilterTable3,
      __DS0FilterTable4,
      __DS0FilterTable5,
      __DS0FilterTable6,
      ""SumReceived"", CALCULATE(SUM('CCST'[Qty])),
      ""Good"", 'CCST'[Good],
      ""Good__"", 'CCST'[Good %]
    ),
    [IsGrandTotalRowTotal],
    1,
    'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Year],
    1,
    [IsDM0Total],
    1,
    'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[MonthNo],
    1,
    'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Month],
    1
  )

ORDER BY
  [IsGrandTotalRowTotal],
  'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Year],
  [IsDM0Total],
  'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[MonthNo],
  'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Month]
  "

So the above is just a hard coded expression, to incorporate parameters in you would take the exising hard coded filters and replace them. So the following month filter

    TREATAS({""November""}, 'CCST'[MonthName])

would become something like the following to manually join in the value from a parameter called Month

    TREATAS({"""  + Parameters!Month.Value + """}, 'CCST'[MonthName])

[JWick1969]

Thank you very much.  By the way, I have another post. about on how to remove the restriction to topN 501 and removed the sub subtotal and Grand Total. Need only the summary.  Kindly take a look my other post. Thanks again.

[JWick1969]

Thank you @d_gosbell  Yes its a bit tricky on building this codeI will try this approach. I have 10 parameter to be created. Hopefully I can build this report which i'm a little bit late on my deadlines. This is my first time to create a report in PBI report builder using DAX Query Performance Analyzer.

[v-janeyg-msft]

@JWick1969  Any updates?

[JWick1969]

Im still working on.  find some error. im not sure if the quotation i put is correct.  below are some of the code which i'm not sure where to put the quotation. 

__DS0FilterTable3,
"Qty", CALCULATE(SUM('CSST'[Qty])),
"Good", 'CSST'[Good],

),
[IsGrandTotalRowTotal],
1,
'CSST'[Category],
1,
[IsDM0Total],
1,

ORDER BY
[IsGrandTotalRowTotal],
'CSST'[Category],
[IsDM0Total],
'CSST'[Client],

[d_gosbell]

So the issue here is that when you mark a parameter as accepting multiple values Report Builder sets it as an array data type and DAX does not support array parameters. By using the JOIN() function you are converting the array to a string, but you query is then comparing the concatenated string to the values in the column which will not work.

You have 3 options here:

1. you could use the RSCustomDaxFilter() function, note this is not a DAX function, but is something that the report builder engine pre-processes (see https://blog.crossjoin.co.uk/2019/11/03/power-bi-report-builder-and-rscustomdaxfilter/) This is what the query builder in Report Builder will generate for multi-value parameters and is probably the simplest approach.
2. You could construct your entire query as a Report Builder expression and manually concatenate the parameter values in to the query, but this can get tricky to debug as you have to do a lot of escaping of quotes.
3. You could use a PATHCONTAINS approach (see https://blog.crossjoin.co.uk/2018/05/16/a-new-approach-to-handling-ssrs-multi-valued-parameters-in-d...)