topic Declaring variables in Power BI Report Builder in DAX Commands and Tips

Declaring variables in Power BI Report Builder

Emmy66 — Mon, 28 Aug 2023 11:17:47 GMT

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.

Re: Declaring variables in Power BI Report Builder

Anonymous — Wed, 30 Aug 2023 06:12:08 GMT

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 - Bing video

Best Regards

Re: Declaring variables in Power BI Report Builder

Emmy66 — Wed, 30 Aug 2023 13:30:29 GMT

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

Re: Declaring variables in Power BI Report Builder

technolog — Sun, 24 Sep 2023 08:12:46 GMT

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.

Re: Declaring variables in Power BI Report Builder

Emmy66 — Mon, 25 Sep 2023 10:43:10 GMT

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.

Re: Declaring variables in Power BI Report Builder

technolog — Mon, 25 Sep 2023 11:08:35 GMT

Anonymous could you look one more time?