DAX Measure giving error

[vishu263]

@curious_Member ,

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

[vishu263]

Hi @tamerj1 ,

It worked perfectly 🙂 Thanks a lot for your help

[tamerj1]

Hi @vishu263 
Please try

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

[vishu263]

@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.

[tamerj1 · Accepted solution]

Hi @vishu263 

please try

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