topic Re: DAX Measure giving error in DAX Commands and Tips

DAX Measure giving error

Anonymous — Tue, 23 May 2023 04:51:33 GMT

Anonymous ,

I am trying to replicate one formula from Business Objects to Power BI DAX which reads as follows.

If ([Type] = "Charted") Then (If IsNull([vCountofChanges] Where([Calendar Days] >= [LeadDays])) Then 0
Else [vCountofChanges] Where([Calendar Days] >= [LeadDays]))
Else (If IsNull([vCountAllChanges] Where([Calendar Days] >= [LeadDays])) Then 0
Else [vCountofChanges] Where([Calendar Days] >= [LeadDays]))

I can only create a measure DAX to replicate above formula . I wrote the following DAX measure but it is giving me an errror. Can someone please correct my DAX.

IF(MAX('Change Requests'[Type]="Charted"),IF(ISBLANK(CALCULATE([vCountofChanges],
FILTER('Change Requests','Change Requests'[Calendar Days]>=[LeadDays]),0,CALCULATE([vCountofChanges],FILTER('Change Requests','Change Requests'[Calendar Days]>=[LeadDays]))))))

Re: DAX Measure giving error

tamerj1 — Tue, 23 May 2023 05:06:15 GMT

Hi Anonymous
Please try

= IF ( MAX ( 'Change Requests'[Type] ) = "Charted", COALESCE ( ISBLANK ( CALCULATE ( [vCountofChanges], FILTER ( 'Change Requests', 'Change Requests'[Calendar Days] >= [LeadDays] ) ) ), 0 ) )

Re: DAX Measure giving error

Anonymous — Tue, 23 May 2023 05:43:52 GMT

tamerj1 Thanks for your quick response.

I used the DAX given by you and named it Test 2. However, its not giving me any values. Ideally, it should give me the total as 2 because there are overall 2 instances where 'Calendar days' are greater than or equal to 'LeadDays'.

Please refer to below snapshot for your reference.

Re: DAX Measure giving error

tamerj1 — Tue, 23 May 2023 07:19:34 GMT

Hi Anonymous

please try

=
IF (
MAX ( 'Change Requests'[Type] ) = "Charted",
SUMX (
VALUES ( 'Change Requests'[Calendar Days] ),
IF ( 'Change Requests'[Calendar Days] >= [LeadDays], [vCountofChanges], 0 )
)
)

Re: DAX Measure giving error

Anonymous — Tue, 23 May 2023 07:27:29 GMT

Hi tamerj1 ,

It worked perfectly 🙂 Thanks a lot for your help