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

[ugoriuko]

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]

[ugoriuko · Accepted solution]

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

[d_gosbell]

---

@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.

[ugoriuko]

@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.

[ugoriuko · Accepted solution]

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

[loicr]

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!

[ugoriuko]

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

[ugoriuko · Accepted solution]

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.