topic Re: Performance Analyzer Query with 2 Parameters Run ok in DAX Studio, No Rows Found in Report Builder in Report Server

Performance Analyzer Query with 2 Parameters Run ok in DAX Studio, No Rows Found in Report Builder

ugoriuko — Tue, 24 Aug 2021 05:16:56 GMT

Hi Guys,

The below Query runs perfectly in DAX Studio, But returns no rows found in Report Builder.

I am passing the report Parameters @Location and @Date as the query parameters

What am I Possibly not doing?

// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({@Location}, 'CSLOCATION'[Location])

VAR __DS0FilterTable2 =
FILTER(
KEEPFILTERS(VALUES('Date Dim'[Date])), 'Date Dim'[YYYY/MM]= @Date)
)

VAR __DS0Core =
SUMMARIZECOLUMNS(
'CSPDETAILS'[Criteria],
__DS0FilterTable,
__DS0FilterTable2,
"Score", 'RM'[Score],
"Result", IGNORE('RM'[Result]),
"Color", IGNORE('RM'[Color])
)

VAR __DS0PrimaryWindowed =
TOPN(1001, __DS0Core, [Score], 0, 'CSPDETAILS'[Criteria], 1)

EVALUATE
__DS0PrimaryWindowed

ORDER BY
[Score] DESC, 'CSPDETAILS'[Criteria]

Re: Performance Analyzer Query with 2 Parameters Run ok in DAX Studio, No Rows Found in Report Builder

d_gosbell — Tue, 24 Aug 2021 09:09:01 GMT

ugoriuko wrote:

The below Query runs on DAX Studio, But returns no rows found in Report Builder.

I am passing the report Parameters @Location and @Date as the query parameters

What am I Possibly not doing?

I can't see how it could run in DAX Studio since your expression for __DS0FilterTable2 is not valid. It includes an extra unmatched closing parathesis and you can't filter using the [YYYY/MM] column when the table you are filtering only contains the values from the [Date] column.

If you can run an All Queries trace against your data source you should be able to capture the full query including the parameter XML block when you run from Report Builder, then you should be able to see what values Report Builder is passing through. Maybe there is some extra formatting happening somewhere and the parameters values are being passed differently to what you expected.

Re: Performance Analyzer Query with 2 Parameters Run ok in DAX Studio, No Rows Found in Report Builder

ugoriuko — Fri, 27 Aug 2021 04:47:32 GMT

d_gosbell

My apologies, I pasted something i edited from the clipboard

This is the correct query.

// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({@Location}, 'CSLOCATION'[Location])

VAR __DS0FilterTable2 =
FILTER(
KEEPFILTERS(VALUES('Date Dim'[Date])), 'Date Dim'[YYYY/MM]= @Date)
)

VAR __DS0Core =
SUMMARIZECOLUMNS(
'CSPDETAILS'[Criteria],
__DS0FilterTable,
__DS0FilterTable2,
"Score", 'RM'[Score],
"Result", IGNORE('RM'[Result]),
"Color", IGNORE('RM'[Color])
)

VAR __DS0PrimaryWindowed =
TOPN(1001, __DS0Core, [Score], 0, 'CSPDETAILS'[Criteria], 1)

EVALUATE
__DS0PrimaryWindowed

ORDER BY
[Score] DESC, 'CSPDETAILS'[Criteria]

I am new to report builder and don't understand what you mean by extra formatting going on.
I will appreciate your kind assistance.

Re: Performance Analyzer Query with 2 Parameters Run ok in DAX Studio, No Rows Found in Report Builder

ugoriuko — Fri, 27 Aug 2021 22:13:46 GMT

So I was able to figure out what was going on.

The Table was returning empty because I had defined the parameters and hadn't passed the values.

When the values are passed, it returned all the correct values but I needed it computed for each row.

Solution
I had to repeat the query for each row I needed to evaluate using the Filter in the Dataset properties

Thanks d_gosbell I really appreciate your genuine and sincere effort at assisting me.

Cheers

Re: Performance Analyzer Query with 2 Parameters Run ok in DAX Studio, No Rows Found in Report Builder

loicr — Thu, 02 Dec 2021 20:41:25 GMT

Hi ugoriuko,

I would appreciate if you can elaborate your solution.

I'm trying to integrate a parameter in a DAX query (from Power BI) but cannot find the solution anywhere!

Re: Performance Analyzer Query with 2 Parameters Run ok in DAX Studio, No Rows Found in Report Builder

ugoriuko — Tue, 14 Dec 2021 02:01:25 GMT

So Apparently, I had to build a query for each row I was trying to format. Then I point each sell to the corresponding query I built just for the formating. You can send me a direct message for further assistance.

Re: Performance Analyzer Query with 2 Parameters Run ok in DAX Studio, No Rows Found in Report Builder

ugoriuko — Tue, 23 Aug 2022 17:38:33 GMT

Have you figured this out? I am so sorry, I have been away from this space for a while