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

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

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

7 Replies

rajendraongole1
Super User
1 year ago
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
  Resolver II
  1 year ago
  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

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

divyed
Super User
1 year ago
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
  Resolver II
  1 year ago
  Hello, thank-you for the reply, however, it is stating a circular dependancy

Forum Discussion

Switch Calculation with Multiple conditions

7 Replies

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