SWITCH measure includes blank/empty rows based on DATEDIFF-column

[Fredde86]

Hello,

I have a datediff column that returns how many days from when my manufacturing orders was actually complete (ITEM_TRANSACTION_HISTORY[Date]) and when the manufacturing order was planned to be complete (MANUFACTURING_ORDERS[Order Due]):

Diff days =
DATEDIFF (
    MAXX ( RELATEDTABLE ( MANUFACTURING_ORDERS ), MANUFACTURING_ORDERS[Order Due] ),
    ITEM_TRANSACTION_HISTORY_SUM[Date],
    DAY
)

and then I have a SWITCH measure that says if DIFFDAYS are 0 or +-1 day within [Order Due] then that is acceptable/"OK", and if they are complete < -1 day then "Too soon" and > 1 days "Too late":

Status Order =

SWITCH(

    TRUE(),

ITEM_TRANSACTION_HISTORY_SUM[Diff days] < -1 , "Too soon" ,

ITEM_TRANSACTION_HISTORY_SUM[Diff days] > 1 , "Too late" ,

ITEM_TRANSACTION_HISTORY_SUM[Diff days] = 1 ||
ITEM_TRANSACTION_HISTORY_SUM[Diff days] = 0 ||
ITEM_TRANSACTION_HISTORY_SUM[Diff days] = -1 , "OK" )

The problem I'm having is that I get "OK" when there is no date in my [Order Due]-column. I'm assuming that is because blank = 0 and 0 = "OK" - but I need to filter out these blanks. I tried with a IF( HASONVALUE( ITEM_TRANSACTION_HISTORY_SUM[Diff days] before my SWITCH measure but it didn´t work.

[Kedar_Pande]

@Fredde86 

Updated Status Order Measure:

Status Order =
SWITCH(
TRUE(),
ISBLANK(RELATED(MANUFACTURING_ORDERS[Order Due])), "No Due Date",
ITEM_TRANSACTION_HISTORY_SUM[Diff days] < -1 , "Too soon",
ITEM_TRANSACTION_HISTORY_SUM[Diff days] > 1 , "Too late",
ITEM_TRANSACTION_HISTORY_SUM[Diff days] = 1 || 
ITEM_TRANSACTION_HISTORY_SUM[Diff days] = 0 || 
ITEM_TRANSACTION_HISTORY_SUM[Diff days] = -1 , "OK"
)

Let me know if you need further assistance!

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[saud968 · Accepted solution]

You can modify your Diff days measure to handle blanks explicitly. Here’s a revised version of your Diff days measure:

Diff days =
IF (
ISBLANK ( MAXX ( RELATEDTABLE ( MANUFACTURING_ORDERS ), MANUFACTURING_ORDERS[Order Due] ) ),
BLANK(),
DATEDIFF (
MAXX ( RELATEDTABLE ( MANUFACTURING_ORDERS ), MANUFACTURING_ORDERS[Order Due] ),
ITEM_TRANSACTION_HISTORY_SUM[Date],
DAY
)
)

This modification checks if the [Order Due] date is blank and returns BLANK() if it is. Otherwise, it calculates the date difference as before.

Next, you can update your Status Order measure to handle these blank values:

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

By adding the ISBLANK check at the beginning of your SWITCH statement, you ensure that any blank values in the Diff days column are handled appropriately and do not return “OK” by default.



Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!