Detecting a Blank in a field

[Anonymous]

Hi All,

I created a column called Days Early or Late. It provides the difference in days between a Received Date column and Scheduled Due Date column. I created a second column that looks at the value in Days Early or Late and returns a value of Early, Late or On Time. Anything two days or more early is Early, two days or more late is Late and anything between 1 day early or late is On Time.

I am hoping to be able to add an additional value of On Order for any entries in Days Early or Late that are blank.

Here's the DAX:

Late or Early = switch ( 
    TRUE(), 
[Days Early or Late] <=-2, "Late",
[Days Early or Late] >=2, "Early",
[Days Early or Late] =1, "On Time",
[Days Early or Late] =-1, "On Time",
[Days Early or Late] =0, "On Time",
[Days Early or Late] =Blank(), "On Order",
"Other")

I've tried specifically to single out the On Time values, but Power BI seems to interpret 0 and Blank as the same. In the Days Early or Late, and future shipments without receipt to stock show a blank value, not a 0.

Sample Data:

Is there a way to have Power BI differentiate between a Blank and a 0 so that I can input the On Order value?

[Anonymous]

Worked like a charm! Thanks!

[AlexisOlson · Accepted solution]

Try this instead:

Late or Early =
SWITCH (
    TRUE (),
    [Days Early or Late] <= -2, "Late",
    [Days Early or Late] >= 2, "Early",
    [Days Early or Late] = 1, "On Time",
    [Days Early or Late] = -1, "On Time",
    ISBLANK ( [Days Early or Late] ), "On Order",
    [Days Early or Late] = 0, "On Time",
    "Other"
)

If that doesn't work, there may be a deeper issue going on with auto-exists.