Adding a Parameter to a Dax Query from Performance Analyzer in Report Builder

[Jdokken]

Hello- 

I am using the query created in the Performance Analyzer in Power Bi but having a difficult time modifing the query to include a date (Text Field) report parameter.  Currently the query has a variable stating to filter the report to May-21 but I want the user to be able to choose which period they would like to print. I tried creating a parameter in report builder and then adding that parameter to each dataset but then I'll get errors like "The parameter @MonthYear is not referred in the query." or "Query contains a parameter which is not declared". Any help would be greatly appreciated!!

Here's the variable that I believe needs to be modified, unless I remove the variable all together and add the @YearMonth somewhere else in the query.

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

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

[dianayu52]

Hello @Jdokken ,

I don’t know if you may already solved this problem, I struggling with a similar problem.

Please refer the below link, reply from Lucian really helpful to solve this issue.

https://forum.enterprisedna.co/t/dax-parameters-with-power-bi-report-builder/14745

[Jdokken]

I am using a query from the Performance Analyzer in Power BI. It does not allow you to create a parameter the same way as if I were using the design mode. Somehow we have to interject the parameter into the copied query in order for the @YearMonth parameter that I created to actually filter the data. Right now our parameter doesn't do anything when we pick a Year Month.  I've set-up parameters that work when we haven't been using a predefined query.  As you can see in the snipit below, the design mode is turned off when you're using a DAX Query. 

[Jdokken]

Has anyone used a query from performance Analyzer and had any luck adding a parameter reference in the Query?

[Anonymous]

Hi @Jdokken ,

Please kindly refer to the document: Report parameters in Power BI Report Builder.

This topic describes the common uses for Power BI Report Builder report parameters, the properties you can set, and much more. Report parameters enable you to control report data, connect related reports together, and vary report presentation. You can use report parameters in paginated reports you create in Report Builder.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jdokken]

It states that the Parameter is not Declared when I try adding the parameter into the Query Designer. 

[Anonymous]

Hi @Jdokken ,

I found related posts and articles, you can refer to

Detect missing parameters in DAX queries

The query contains the XXXXXName parameter, which is not declared. SSRS2008/MDX query

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jdokken]

Thanks, I tried adding the Parameter in the Parameters Toolbar button in the Query Designer window. Once I do that and then try to add it in the Query (which I'm not sure if I'm doing it correctly) gives me a message of "Query Preparation failed". I Tried using, 

TREATAS({@YearMonth}, 'Period'[Year Month])  but that gave me the same message. 

[Anonymous]

Hi @Jdokken ,

You may try to contact Power BI team for help by creating a support ticket.

Website: https://powerbi.microsoft.com/en-us/support/

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jdokken]

Okay, I tried submitting a support ticket. 

[Jdokken]

Does anyone have resources or ideas? 🙂