Switch Calculation with Multiple conditions

[villa1980]

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 = SWITCH(

    TRUE(),

    'EXTR_STG_FOCUS APPOINTMENTS'[Bay_Code_No] = "BAY 1" && 'EXTR_STG_FOCUS APPOINTMENTS'[Duration] = -60 , FORMAT('EXTR_STG_FOCUS APPOINTMENTS'[STARTDATE],"DD Mmmm YYYY") ,

    'EXTR_STG_FOCUS APPOINTMENTS'[Bay_Code_No] = "BAY 2" && 'EXTR_STG_FOCUS APPOINTMENTS'[Duration] = -60, 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") ,

     ""

     )

For example Bay 1= Duration is -60 or Duration is = -120
                     Bay 2 = Duration is -60 or Duration is = -120 or Duration = -180.
I tried using || but this did not seem to work, any help would be appreciated. 
Thanks

[divyed]

Hello @villa1980 ,

Try below dax for this :

Next 60 min Block Date =
VAR MaxDate =
CALCULATE(
MAX('EXTR_STG_FOCUS APPOINTMENTS'[STARTDATE]),
FILTER(
'EXTR_STG_FOCUS APPOINTMENTS',
'EXTR_STG_FOCUS APPOINTMENTS'[Bay_Code_No] = 'EXTR_STG_FOCUS APPOINTMENTS'[Bay_Code_No]
)
)
RETURN
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"),

FORMAT(MaxDate + 1, "DD Mmmm YYYY")
)

I hope this helps.

Did I answer your question ? Mark this as solution or give a thumbs up if this helps.

Cheers

[villa1980]

Hello, thank-you for the reply, however, it is stating a circular dependancy

[Bibiano_Geraldo]

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]

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 · Accepted solution]

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

[rajendraongole1]

Hi @villa1980 - I have modify your formula to incorporate the additional Duration conditions for each bay:

please find the below:

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

""
)

Check the and let me know.

[villa1980]

Hi, THank-you for that, it has worked very well, something very simple like adding brackets 🙂
I have another issue now, at the end of DAX I return a "" if none of the conditions are met. "" look messy in my table, so my thought is saying the MAX Date for the BAY +1...
eg BAY 1 is blank MAX DATE is 18th Nov 24, so would like to say 19th Nov 24
     Bay 4 is blank MAX DATE is 29th Nov 24, so would like to say 30th Nov 24