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
jgray72's avatar
jgray72
Icon for Helper II rankHelper II
6 years ago

I've also tried playing around with the @, but it won't work either.

 

 

JG

d_gosbell's avatar
d_gosbell
Icon for Super User rankSuper User
5 years ago

So one way to do this is to add a filter to your visual in Power BI for the Brand and Season. This will add an "_DS0FilterXXX" variable to your query and inject it into the appropriate place in the SUMMARIZECOLUMNS expression.

 

So if you set a Brand filter of "XYZ" and a season filter of "Winter" the you would just need to find these two strings in the query you copy from the Performance Analyzer and then replace the hard coded values with a reference to @Brand and @Season (or you could use the Parameters!Brand.Value string expression approach)

  • Jdokken's avatar
    Jdokken
    Icon for Helper III rankHelper III
    5 years ago

    When I try changing the "APR-21" to @YearMonth, I get a message saying the parameter is not declared. 

       VAR __DS0FilterTable3 = TREATAS({"APR-21"}, 'Period'[GL Period])

     

    • blynchdata's avatar
      blynchdata
      Icon for Resolver I rankResolver I
      5 years ago

      When I receive this error, I have to add the query parameters here in the Query Designer, then that error about it being declared goes away.

       

      • Jdokken's avatar
        Jdokken
        Icon for Helper III rankHelper III
        5 years ago

        How did you write it into the query?  As a Variable like this?

        VAR __DS0FilterTable3 =
        TREATAS({"@YearMonth"}, 'Period'[GL Period])

         

        Here's my full DAX Query from Power BI's Performance Analyzer. 

        // DAX Query
        DEFINE
        MEASURE 'Internal Financial Metrics'[-Mgn R&O Filter] =
        (/* USER DAX BEGIN */
        IF(
        ([CY R&O] = 0 || [CY R&O]= BLANK()) &&
        ([NY R&O] = 0 || [NY R&O]= BLANK()) &&
        ([FY R&O] = 0 || [FY R&O]= BLANK()),
        "Don't Include", "Include")


        /* USER DAX END */)

        VAR __DS0FilterTable =
        FILTER(
        KEEPFILTERS(VALUES('Internal Financial Attributes'[Project Status - Reporting])),
        NOT('Internal Financial Attributes'[Project Status - Reporting] = "IATP")
        )

        VAR __DS0FilterTable2 =
        TREATAS({"Y"}, 'Reporting Filters'[Filter for Teaming View])

        VAR __DS0FilterTable3 =
        TREATAS({"MAY-21"}, 'Period'[GL Period])

        VAR __DS0FilterTable4 =
        TREATAS({"Thousands"}, 'Reporting Multiples'[Reporting Units])


        VAR __ValueFilterDM1 =
        FILTER(
        KEEPFILTERS(
        SUMMARIZECOLUMNS(
        'Internal Financial Attributes'[Group Name],
        'Internal Financial Attributes'[GROUPSORTORDER],
        'Project'[Project Name],
        __DS0FilterTable,
        __DS0FilterTable2,
        __DS0FilterTable3,
        __DS0FilterTable4,
        "ATD___Complete", 'Internal Financial Metrics'[ATD % Complete],
        "CY_R_O", 'Internal Financial Metrics'[CY R&O],
        "NY_R_O", 'Internal Financial Metrics'[NY R&O],
        "FY_R_O", 'Internal Financial Metrics'[FY R&O],
        "Total_R_O", 'Internal Financial Metrics'[Total R&O],
        "Cash_Position", 'Internal Financial Metrics'[Cash Position],
        "R_O___Group_Margin_Fcst", 'Internal Financial Metrics'[R&O + Group Margin Fcst],
        "MinRisk___Opportunities_Comments", CALCULATE(MIN('Internal Financial Attributes'[Risk & Opportunities Comments])),
        "-Mgn R&O Filter", IGNORE('Internal Financial Metrics'[-Mgn R&O Filter])
        )
        ),
        [-Mgn R&O Filter] = "Include"
        )


        EVALUATE
        __ValueFilterDM1