Calculate not working with one SWITCH statement but with another?

[Fredde86]

Hi,

I have a SWITCH measure that goes like this and it's working like intended:

Status Order *NEW* = 
SWITCH( 
    TRUE(), 
        ISBLANK( ITEM_TRANSACTION_HISTORY_SUM[NetWorkdays *History*] ), BLANK() ,
        ITEM_TRANSACTION_HISTORY_SUM[NetWorkdays *History*] < -1 , "Too soon" ,
        ITEM_TRANSACTION_HISTORY_SUM[NetWorkdays *History*] > 1 , "Too late" ,
        ITEM_TRANSACTION_HISTORY_SUM[NetWorkdays *History*] = 1 || ITEM_TRANSACTION_HISTORY_SUM[NetWorkdays *History*] = 0 || ITEM_TRANSACTION_HISTORY_SUM[NetWorkdays *History*] = -1 , "OK" )

I then want to count how many Orders that are "Status OK" or "Status Too soon" & "Too late" to determine if they are within acceptable delay within Order Due Date.

My two measures for "OK" and "Too soon / Too late" are identical, see below:

Status (OK) = 
CALCULATE( [Produced] ,
FILTER( ITEM_TRANSACTION_HISTORY_SUM ,
        ITEM_TRANSACTION_HISTORY_SUM[Status Order *NEW*] = "OK" ))

Status (Too soon / Too late) = 
CALCULATE( [Produced] ,
FILTER( ITEM_TRANSACTION_HISTORY_SUM ,
        ITEM_TRANSACTION_HISTORY_SUM[Status Order *NEW*] = "Too soon" ||
        ITEM_TRANSACTION_HISTORY_SUM[Status Order *NEW*] = "Too late" )
)

I do get values for "OK" IF the diff is 0, but none for if "OK" is +-1 days or "Too soon / Too late", see below:

Thanks in advance!

[rajendraongole1 · Accepted solution]

Hi @Fredde86 - you can adjust your measures to ensure they work correctly for Ok, Too soon and Too Late measures as below:

Status (OK) =
CALCULATE(
SUM(ITEM_TRANSACTION_HISTORY_SUM[Produced]),
ITEM_TRANSACTION_HISTORY_SUM[NetWorkdays *History*] IN {-1, 0, 1}
)

Status (Too soon or Late)

Status (Too soon / Too late) =
CALCULATE(
SUM(ITEM_TRANSACTION_HISTORY_SUM[Produced]),
ITEM_TRANSACTION_HISTORY_SUM[NetWorkdays *History*] < -1 ||
ITEM_TRANSACTION_HISTORY_SUM[NetWorkdays *History*] > 1
)

Calculated columns are evaluated at the row level during data refresh and may not respond dynamically to slicers or other filters applied in the visual. Using measure logic directly avoids this limitation and ensures that the calculations respect the current filter context.

Hope this works.

[Fredde86]

Hi @rajendraongole1, thanks for your reply!

I made your proposed changes to my two measures, but still the same outcome - only when diff is 0 gets calculated:

Could i have something to do with my NetWorkdays measure, that it looks like -1 and +1 in the table but not when Calculate sums it?

NetWorkdays Delay *History* = 
SUMX(
    FILTER(
        MANUFACTURING_ORDERS_HISTORY,
        MANUFACTURING_ORDERS_HISTORY[Order nr *History*] = MAX(ITEM_TRANSACTION_HISTORY_SUM[Order nr]) &&
        MANUFACTURING_ORDERS_HISTORY[Finish_Item_Number] = MAX(ITEM_TRANSACTION_HISTORY_SUM[Item])
    ),
    VAR DueDate = MANUFACTURING_ORDERS_HISTORY[Due Date *History*]
    VAR CompletionDate = MAX(ITEM_TRANSACTION_HISTORY_SUM[Date])
    VAR NetworkDays =
        NETWORKDAYS(
            DueDate,
            CompletionDate
        , 1, 'Helgdagar&Semester' )
    RETURN
                IF(
            DueDate = CompletionDate,
            0,
            IF(
                DueDate > CompletionDate,
                NetworkDays + 1,
                NetworkDays - 1
            )
        )
)