Forum Discussion

villa1980

Resolver II

1 year ago

Solved

Switch Calculation with Multiple conditions

Hi All, I have the below DAX formula which highlights the date based on the duration. This works well but what I need to do now is to add a few conditions to this... Next 60 min Block Date = SWITC...

Need Help

Bibiano_Geraldo

1 year ago

Hi,

To get the maximum date for each group, you can use the MAX function within a CALCULATE statement to ensure you get the maximum date for each Bay_Code_No group. Here’s an updated version of your DAX formula:

Next 60 min Block Date = 
SWITCH(
    TRUE(),
    'EXTR_STG_FOCUS APPOINTMENTS'[Bay_Code_No] = "BAY 1" && 
    ('EXTR_STG_FOCUS APPOINTMENTS'[Duration] = -60 || 'EXTR_STG_FOCUS APPOINTMENTS'[Duration] = -120), 
    FORMAT(
        CALCULATE(
            MAX('EXTR_STG_FOCUS APPOINTMENTS'[STARTDATE]), 
            'EXTR_STG_FOCUS APPOINTMENTS'[Bay_Code_No] = "BAY 1",
            'EXTR_STG_FOCUS APPOINTMENTS'[Duration] IN {-60, -120}
        ),
        "DD Mmmm YYYY"
    ),
    
    'EXTR_STG_FOCUS APPOINTMENTS'[Bay_Code_No] = "BAY 2" && 
    ('EXTR_STG_FOCUS APPOINTMENTS'[Duration] = -60 || 'EXTR_STG_FOCUS APPOINTMENTS'[Duration] = -120 || 'EXTR_STG_FOCUS APPOINTMENTS'[Duration] = -180), 
    FORMAT(
        CALCULATE(
            MAX('EXTR_STG_FOCUS APPOINTMENTS'[STARTDATE]), 
            'EXTR_STG_FOCUS APPOINTMENTS'[Bay_Code_No] = "BAY 2",
            'EXTR_STG_FOCUS APPOINTMENTS'[Duration] IN {-60, -120, -180}
        ),
        "DD Mmmm YYYY"
    ),
    
    'EXTR_STG_FOCUS APPOINTMENTS'[Bay_Code_No] = "BAY 3" && 
    'EXTR_STG_FOCUS APPOINTMENTS'[Duration] = -60, 
    FORMAT(
        CALCULATE(
            MAX('EXTR_STG_FOCUS APPOINTMENTS'[STARTDATE]), 
            'EXTR_STG_FOCUS APPOINTMENTS'[Bay_Code_No] = "BAY 3",
            'EXTR_STG_FOCUS APPOINTMENTS'[Duration] = -60
        ),
        "DD Mmmm YYYY"
    ),
    
    'EXTR_STG_FOCUS APPOINTMENTS'[Bay_Code_No] = "BAY 4" && 
    'EXTR_STG_FOCUS APPOINTMENTS'[Duration] = -60, 
    FORMAT(
        CALCULATE(
            MAX('EXTR_STG_FOCUS APPOINTMENTS'[STARTDATE]), 
            'EXTR_STG_FOCUS APPOINTMENTS'[Bay_Code_No] = "BAY 4",
            'EXTR_STG_FOCUS APPOINTMENTS'[Duration] = -60
        ),
        "DD Mmmm YYYY"
    ),
    
    'EXTR_STG_FOCUS APPOINTMENTS'[Bay_Code_No] = "BAY 5" && 
    'EXTR_STG_FOCUS APPOINTMENTS'[Duration] = -60, 
    FORMAT(
        CALCULATE(
            MAX('EXTR_STG_FOCUS APPOINTMENTS'[STARTDATE]), 
            'EXTR_STG_FOCUS APPOINTMENTS'[Bay_Code_No] = "BAY 5",
            'EXTR_STG_FOCUS APPOINTMENTS'[Duration] = -60
        ),
        "DD Mmmm YYYY"
    ),
    
    IF(
        ISBLANK('EXTR_STG_FOCUS APPOINTMENTS'[STARTDATE]),
        FORMAT(MAX('EXTR_STG_FOCUS APPOINTMENTS'[STARTDATE]) + 1, "DD Mmmm YYYY"),
        ""
    )
)

Bibiano_Geraldo

Super User

1 year ago

Hi, villa1980 ,
To add multiple conditions to your DAX formula, you can use the OR function within the SWITCH statement. Additionally, to handle the blank cases and return the next day after the maximum date, you can use the MAX function combined with IF to check for blank values. Here’s how you can modify your formula:

Next 60 min Block Date = 
SWITCH(
    TRUE(),
    'EXTR_STG_FOCUS APPOINTMENTS'[Bay_Code_No] = "BAY 1" && 
    ('EXTR_STG_FOCUS APPOINTMENTS'[Duration] = -60 || 'EXTR_STG_FOCUS APPOINTMENTS'[Duration] = -120), 
    FORMAT('EXTR_STG_FOCUS APPOINTMENTS'[STARTDATE],"DD Mmmm YYYY"),
    
    'EXTR_STG_FOCUS APPOINTMENTS'[Bay_Code_No] = "BAY 2" && 
    ('EXTR_STG_FOCUS APPOINTMENTS'[Duration] = -60 || 'EXTR_STG_FOCUS APPOINTMENTS'[Duration] = -120 || 'EXTR_STG_FOCUS APPOINTMENTS'[Duration] = -180), 
    FORMAT('EXTR_STG_FOCUS APPOINTMENTS'[STARTDATE],"DD Mmmm YYYY"),
    
    'EXTR_STG_FOCUS APPOINTMENTS'[Bay_Code_No] = "BAY 3" && 
    'EXTR_STG_FOCUS APPOINTMENTS'[Duration] = -60, 
    FORMAT('EXTR_STG_FOCUS APPOINTMENTS'[STARTDATE],"DD Mmmm YYYY"),
    
    'EXTR_STG_FOCUS APPOINTMENTS'[Bay_Code_No] = "BAY 4" && 
    'EXTR_STG_FOCUS APPOINTMENTS'[Duration] = -60, 
    FORMAT('EXTR_STG_FOCUS APPOINTMENTS'[STARTDATE],"DD Mmmm YYYY"),
    
    'EXTR_STG_FOCUS APPOINTMENTS'[Bay_Code_No] = "BAY 5" && 
    'EXTR_STG_FOCUS APPOINTMENTS'[Duration] = -60, 
    FORMAT('EXTR_STG_FOCUS APPOINTMENTS'[STARTDATE],"DD Mmmm YYYY"),
    
    IF(
        ISBLANK('EXTR_STG_FOCUS APPOINTMENTS'[STARTDATE]),
        FORMAT(MAX('EXTR_STG_FOCUS APPOINTMENTS'[STARTDATE]) + 1, "DD Mmmm YYYY"),
        ""
    )
)

if this help ypu, please mark as solution and give a Kudo.

thank you

villa1980

Resolver II

1 year ago

Hi, thank-you for the reply, it does work however, it gives me 01 January 2025 for all Bays, I just need the max date for each group.

Bibiano_Geraldo

Super User

1 year ago

Hi,

Next 60 min Block Date = 
SWITCH(
    TRUE(),
    'EXTR_STG_FOCUS APPOINTMENTS'[Bay_Code_No] = "BAY 1" && 
    ('EXTR_STG_FOCUS APPOINTMENTS'[Duration] = -60 || 'EXTR_STG_FOCUS APPOINTMENTS'[Duration] = -120), 
    FORMAT(
        CALCULATE(
            MAX('EXTR_STG_FOCUS APPOINTMENTS'[STARTDATE]), 
            'EXTR_STG_FOCUS APPOINTMENTS'[Bay_Code_No] = "BAY 1",
            'EXTR_STG_FOCUS APPOINTMENTS'[Duration] IN {-60, -120}
        ),
        "DD Mmmm YYYY"
    ),
    
    'EXTR_STG_FOCUS APPOINTMENTS'[Bay_Code_No] = "BAY 2" && 
    ('EXTR_STG_FOCUS APPOINTMENTS'[Duration] = -60 || 'EXTR_STG_FOCUS APPOINTMENTS'[Duration] = -120 || 'EXTR_STG_FOCUS APPOINTMENTS'[Duration] = -180), 
    FORMAT(
        CALCULATE(
            MAX('EXTR_STG_FOCUS APPOINTMENTS'[STARTDATE]), 
            'EXTR_STG_FOCUS APPOINTMENTS'[Bay_Code_No] = "BAY 2",
            'EXTR_STG_FOCUS APPOINTMENTS'[Duration] IN {-60, -120, -180}
        ),
        "DD Mmmm YYYY"
    ),
    
    'EXTR_STG_FOCUS APPOINTMENTS'[Bay_Code_No] = "BAY 3" && 
    'EXTR_STG_FOCUS APPOINTMENTS'[Duration] = -60, 
    FORMAT(
        CALCULATE(
            MAX('EXTR_STG_FOCUS APPOINTMENTS'[STARTDATE]), 
            'EXTR_STG_FOCUS APPOINTMENTS'[Bay_Code_No] = "BAY 3",
            'EXTR_STG_FOCUS APPOINTMENTS'[Duration] = -60
        ),
        "DD Mmmm YYYY"
    ),
    
    'EXTR_STG_FOCUS APPOINTMENTS'[Bay_Code_No] = "BAY 4" && 
    'EXTR_STG_FOCUS APPOINTMENTS'[Duration] = -60, 
    FORMAT(
        CALCULATE(
            MAX('EXTR_STG_FOCUS APPOINTMENTS'[STARTDATE]), 
            'EXTR_STG_FOCUS APPOINTMENTS'[Bay_Code_No] = "BAY 4",
            'EXTR_STG_FOCUS APPOINTMENTS'[Duration] = -60
        ),
        "DD Mmmm YYYY"
    ),
    
    'EXTR_STG_FOCUS APPOINTMENTS'[Bay_Code_No] = "BAY 5" && 
    'EXTR_STG_FOCUS APPOINTMENTS'[Duration] = -60, 
    FORMAT(
        CALCULATE(
            MAX('EXTR_STG_FOCUS APPOINTMENTS'[STARTDATE]), 
            'EXTR_STG_FOCUS APPOINTMENTS'[Bay_Code_No] = "BAY 5",
            'EXTR_STG_FOCUS APPOINTMENTS'[Duration] = -60
        ),
        "DD Mmmm YYYY"
    ),
    
    IF(
        ISBLANK('EXTR_STG_FOCUS APPOINTMENTS'[STARTDATE]),
        FORMAT(MAX('EXTR_STG_FOCUS APPOINTMENTS'[STARTDATE]) + 1, "DD Mmmm YYYY"),
        ""
    )
)

Forum Discussion

Switch Calculation with Multiple conditions

Related content

Multiple conditions Index

Conditional formatting using multiple calculation groups

Multiple Donut charts with conditions

Conditional Column with Multiple Conditions

Conditional formatting on calculated item in calculation group