Forum Discussion

Fredde86's avatar
Fredde86
Icon for Advocate I rankAdvocate I
1 year ago
Solved

Calculate not working with one SWITCH statement but with another?

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*...
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    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.

     

rajendraongole1's avatar
rajendraongole1
Icon for Super User rankSuper User
1 year ago

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's avatar
Fredde86
Icon for Advocate I rankAdvocate I
1 year ago

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