topic Re: Power Bi Report Builder - DAX Query from Performance analyzer multiple parameter values issue in Report Server

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

Anonymous — Mon, 29 Nov 2021 09:16:30 GMT

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!

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

d_gosbell — Mon, 29 Nov 2021 09:50:20 GMT

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:

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.
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.
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-dax-queries/)

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

Anonymous — Mon, 29 Nov 2021 13:07:21 GMT

Thank you for the reply. By the way, How can I change the code for TREATAS to accomodate multiple values.

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

d_gosbell — Mon, 29 Nov 2021 21:30:53 GMT

@Anonymous wrote:

Thank you for the reply. By the way, How can I change the code for TREATAS to accomodate multiple values.

Treatas will accomodate multiple values, the problem is mapping the parameter from Report Builder to the parameter in the query.

If we take the top 3 lines of your query as an example

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

Currently with the JOIN in your parameter expression this will expand to the following with a single string value:

// DAX Query DEFINE VAR __DS0FilterTable = TREATAS({"2019|2020"}, 'DateTable'[Received Year])

What it needs to be is something like the following (assuming your Year is an integer column)

// DAX Query DEFINE VAR __DS0FilterTable = TREATAS({2019,2020}, 'DateTable'[Received Year])

The only way I know to use TREATAS is to click the fx button on the query and build the query as a dynamic expression using something like the following (option 2 from my first post)

You will noticed the red doubling up of quotes that is required in order to embed a quote inside a quoted string expression. This is the tricky bit of building your query this way. If you don't escape all the quotes correctly the report will not run and it can be tricky to find mistakes. But once you have it working it functions well. I have one report I build like this which is has about 15 optional parameters so I actually include/exclude entire sections of the query based on whether the parameters have a value or not.

// DAX Query
DEFINE

VAR __DS0FilterTable =
TREATAS({" + Join(Parameters!Year.Value,",") + "}, 'DateTable'[Received Year])

VAR __DS0FilterTable1 =
TREATAS({""" + Join(Parameters!Month.Value,""",""") + """}, '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"","

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

Anonymous — Mon, 06 Dec 2021 12:32:31 GMT

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.

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

v-janeyg-msft — Wed, 08 Dec 2021 03:20:11 GMT

@Anonymous Any updates?

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

Anonymous — Wed, 08 Dec 2021 05:42:20 GMT

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],

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

Anonymous — Wed, 08 Dec 2021 09:30:53 GMT

HI,

I'm having an error could not figure out on where to put the quotes which I used your sample. Anyway, I'm insert new DAX Query and optimize some of the column to shorten the code.

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]

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

d_gosbell — Thu, 09 Dec 2021 02:42:46 GMT

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])

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

Anonymous — Thu, 09 Dec 2021 07:41:07 GMT

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.

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

Anonymous — Fri, 10 Dec 2021 00:49:41 GMT

This is what I did. I create a new dataset and click the properties then on the query type I paste the query and tried also to paste the query in expression(Fx). Click the fields properties then click the add select the query field and filled up the field source and fieldname which is coming from the query. then I run the the report and No data displayed.

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

d_gosbell — Fri, 10 Dec 2021 01:54:47 GMT

It will be throwing an error somewhere since expressions in the fields in Report Builder use VBA (since they need to be data source agnostic) and what you have there is DAX. I tend avoid using calculated fields in Report Builder where ever possible and put things like that into the query so that they get executed by the tabular engine which will give you much better performance.

And I actually missed a step in my workflow. I actually paste the un-altered query in and refresh the fields first, then I click the fx and turn it into an expression. That maps the query fields to fields in report builder which you can then drag and drop onto your report

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

Anonymous — Fri, 10 Dec 2021 04:07:54 GMT

I'm not experiencing an error. so far i encountered no data displayed on my report. Imap the query field to field by copying the one from the query code.

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

d_gosbell — Fri, 10 Dec 2021 04:24:49 GMT

@Anonymous wrote:

I'm not experiencing an error. so far i encountered no data displayed on my report. Imap the query field to field by copying the one from the query code.

This will be the cause of your problem. Because of the way Report Builder executes dax queries certain characters in the fields names get replaced with underscores. So if you have not mapped using the correctly encoded names you will just get an empty string in your fields. So if you have asked for the field "[Sales Amount]" but the query engine in Report Builder returns it as "__Sales_Amount__" then your dataset will contain rows, but all the fields will be blank.

It is much easier to use the approach I suggested in my previous reply and paste in the raw query first and let Report Builder generate the field mappings before you turn your query into an expression.

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

Anonymous — Fri, 10 Dec 2021 08:59:35 GMT

Thank you for sharing this technique and I learned new knowledge on PowerBI report builder. I followed your approach and there's already data displayed on the report.🙂👍

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

Anonymous — Tue, 14 Dec 2021 01:45:24 GMT

The report is already working if i choose one year data. but i'm having issues if i pulled data three years data. I have 33 columns on my report. May I know what are the fixes should I do in regards with this error. Thanks. by the way, below is the error.

Resource Governing: This query uses more memory than the configured limit.
The query — or calculations referenced by it — might be too memory-intensive to run.
To run this query, you could simplify the query or its calculations, or reach out to your capacity administrator to see if they can increase the per-query memory limit.

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

d_gosbell — Tue, 14 Dec 2021 03:56:33 GMT

So the two possible fixes for this are spelled out in the error message itself.

@Anonymous wrote:

To run this query, you could simplify the query or its calculations, or reach out to your capacity administrator to see if they can increase the per-query memory limit.

As long as you are not trying to export millions of records the issue is most likely one of your measures requiring a lot of memory to calculate.

I would suggest copying your query out to something like DAX Studio or SSMS where you can easily modify it an run it repeatedly. Then comment out all your measures and include a simple measure like one that is just a SUM of a single column or a COUNTROWS. If the query works with a simple measure then gradually add your other measures back in one at a time until you find which one(s) cause the issue. The you will need to see if you can re-write / optimize those.

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

Anonymous — Tue, 14 Dec 2021 09:52:16 GMT

Thank you for your reply. by the way, I notice one of my parameter. I'm selecting all the values meaning i have 30 values and it show all the 30 items in my report parameter if i choose (select All), How can i display only the "All" on my Report. curretnly i did like this in my parameter expression =Join(Parameters!Year.Value,",").

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

d_gosbell — Tue, 14 Dec 2021 21:58:43 GMT

@Anonymous wrote:

Thank you for your reply. by the way, I notice one of my parameter. I'm selecting all the values meaning i have 30 values and it show all the 30 items in my report parameter if i choose (select All), How can i display only the "All" on my Report. curretnly i did like this in my parameter expression =Join(Parameters!Year.Value,",").

'

You could check in your expression if the number of values selected in the parameter matches the number of rows in the dataset you use to populate the parameter. So the expression would look something like the following, you would just need to change the field name and dataset names in red to match those in your report.

=iif( Parameters!Year.Value.Length = Count(Fields!Year.Value, "YearDataSet"), "ALL", Join(Parameters!Year.Value, ","))

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

Anonymous — Wed, 26 Jan 2022 22:10:28 GMT

I have a similiar case but instead of using parameters for the filter I want to use parameters to dynamically bring a different field. The problem with that is that summrize columns does not give aliases to the columns So I wouldn't know what to name it in the Fields section. I tried Nesting SelectedColumns on top of Summarize but then I would get empty values for "Actuals" column

="DEFINE VAR __DS0Core = SUMMARIZECOLUMNS( 'Dim_Acc'[GEN_MEMBER3_DESC], 'Dim_Acc'[GEN_MEMBER4_DESC], 'Dim_Acc'[GEN_MEMBER4_ID], 'Dim_Acc'[GEN_MEMBER5_DESC], 'Dim_Acc'[GEN_MEMBER5_ID], 'Dim_CostCenter'[Segment ID Desc], (""" + Parameters!Field.Value + """), ""Actuals MTD"", 'Financial_Measures'[Actuals MTD] ) EVALUATE __DS0Core "