Forum Discussion

jgray72's avatar
jgray72
Icon for Helper II rankHelper II
6 years ago

Parameters in Report Builder (DAX query copied from Performance Analyzer)

Hi - I'm building my first report in Power BI Report Builder and followed instructions to copy my query from Performance Analyzer.  The query works as expected and it' s a huge time saver to copy it ...
Need Help
ugoriuko's avatar
ugoriuko
Frequent Visitor
5 years ago

Hi d_gospel,

 

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?

 

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

 

d_gosbell's avatar
d_gosbell
Icon for Super User rankSuper User
5 years ago
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's avatar
    ugoriuko
    Frequent Visitor
    5 years ago

    Thanks for the feedback. I actually have that properly setup as

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

    VAR __DS0FilterTable2 =
    FILTER(
    KEEPFILTERS(VALUES('Date Dim'[YYYY/MM])), '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 must have pasted something else from my clipboard.

     

    What do you mean by extra formatting?
    I am new to report builder and have no idea what is going on.

    • d_gosbell's avatar
      d_gosbell
      Icon for Super User rankSuper User
      5 years ago
      ugoriuko wrote:

      What do you mean by extra formatting?

      So if your @date parameter is setup as a datetime data type you might find it is actually sending through the data in a format something like "dd/mm/yyyy" instead of "yyyy/mm". If you use the All Queries trace in DAX Studio or if you use SQL Profiler and capture the BeginQuery event you will be able to see the values the parameters is passing through.

       

      The other trick I've used sometimes is to temporarily put text boxes on my report linked to the parameters so that I can see the parameter values being passed through that way.