Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

DAX Measure giving error

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

@Dax
@dax @help
@Dax help
@Dax Measure Error
@Daxhelp
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi Anonymous 

    please try

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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi tamerj1 ,

    It worked perfectly 🙂 Thanks a lot for your help

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi Anonymous 
    Please try

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

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        tamerj1
        Community Champion
        3 years ago

        Hi Anonymous 

        please try

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