Declaring variables in Power BI Report Builder

[Emmy66]

Hi,

I'm currently struggling to get this to work in powerbi report builder.

 EVALUATE
CALCULATETABLE (
    SUMMARIZE (
        'Rejected D2',
        'Rejected D2'[PointReference],
        'Rejected D2'[ReasonForRequest],
        'Rejected D2'[SenderParticipantId],
        'Rejected D2'[MessageDateTime],
        'Rejected D2'[Market],
        'Rejected D2'[FailureReason],
        'Rejected D2'[Case Number],
        'Rejected D2'[Case Category],
        'Rejected D2'[AdditionalInformation]
    ),
    'Rejected D2'[MessageDateTime] >= @StartDate,
    'Rejected D2'[MessageDateTime] <= @EndDate,
    'Rejected D2'[Market] IN ( { @Market } )
)

This query runs in Dax Studio with a prompt to enter the values but when I transferred it to pbi report builder, it throws an error message "The query contains the 'StartDate' parameter, which is not declared"

Could someone please guide me on how I should declare these variables and also, I want @Market to accept multi value input

Your assistance on this will be highly appreciated.

[technolog]

I hope this message finds you well. I've noticed that this solution remain unresolved. If any of you have managed to find a resolution to the issue, I kindly request that you share your solution for the benefit of the entire community. Alternatively, if you're still facing challenges, please do let us know as well.

Your insights and updates will greatly assist others who might be encountering the same challenge.

[Emmy66]

@technolog , unfortunately, I've not been able to resolve this and don't have any solution. Would still be appreciated if someone out there can help.

[technolog]

@Anonymous could you look one more time?

[Anonymous]

Hi @Emmy66 ,

According to the formula which you provided, it involves 3 parameters(two single-value parameters:@StartDate&@EndDate and one multi-value parameter: @Market ). You can refer the following links to get it:

Report parameters in Power BI Report Builder - Power BI | Microsoft Learn

Multi-value parameter:

Add a multi-value parameter to a paginated report - Power BI | Microsoft Learn

All About Multi Value Parameters in Power BI Paginated Report | Tips and Tricks in Paginated Reports...

Best Regards

[Emmy66]

Thanks @Anonymous  for your response. Below is the query generated thru query designer which I'm trying to find a way to simplify, hence my earlier query.

DEFINE VAR vFromRejectedD2MessageDateTime1 = IF(PATHLENGTH(@FromRejectedD2MessageDateTime) = 1, IF(@FromRejectedD2MessageDateTime <> "", @FromRejectedD2MessageDateTime, BLANK()), IF(PATHITEM(@FromRejectedD2MessageDateTime, 2) <> "", PATHITEM(@FromRejectedD2MessageDateTime, 2), BLANK()))

VAR vFromRejectedD2MessageDateTime1ALL = PATHLENGTH(@FromRejectedD2MessageDateTime) > 1 && PATHITEM(@FromRejectedD2MessageDateTime, 1, 1) < 1

VAR vToRejectedD2MessageDateTime1 = IF(PATHLENGTH(@ToRejectedD2MessageDateTime) = 1, IF(@ToRejectedD2MessageDateTime <> "", @ToRejectedD2MessageDateTime, BLANK()), IF(PATHITEM(@ToRejectedD2MessageDateTime, 2) <> "", PATHITEM(@ToRejectedD2MessageDateTime, 2), BLANK()))

VAR vToRejectedD2MessageDateTime1ALL = PATHLENGTH(@ToRejectedD2MessageDateTime) > 1 && PATHITEM(@ToRejectedD2MessageDateTime, 1, 1) < 1

EVALUATE SUMMARIZECOLUMNS('Rejected D2'[PointReference], 'Rejected D2'[SenderParticipantId], 'Rejected D2'[Market], 'Rejected D2'[ReasonForRequest], 'Rejected D2'[Case Category], 'Rejected D2'[Case Number], 'Rejected D2'[AdditionalInformation], 'Rejected D2'[FailureReason], 'Rejected D2'[MessageDateTime], FILTER(VALUES('Rejected D2'[MessageDateTime]), (vFromRejectedD2MessageDateTime1ALL || 'Rejected D2'[MessageDateTime] >= DATEVALUE(vFromRejectedD2MessageDateTime1) + TIMEVALUE(vFromRejectedD2MessageDateTime1)) && (vToRejectedD2MessageDateTime1ALL || 'Rejected D2'[MessageDateTime] <= DATEVALUE(vToRejectedD2MessageDateTime1) + TIMEVALUE(vToRejectedD2MessageDateTime1))), RSCustomDaxFilter(@RejectedD2Market,EqualToCondition,[Rejected D2].[Market],String))