SWITCH not checking 0s properly

[olimilo]

Have an odd case here, I am double checking the values in our dashboard and the output of this formula is not reflecting the correct value for the ones with 0s which should fall under "On Time":

If I move the 'Audits'[FieldTAT] = BLANK() condition to the bottom, I get the error "Expressions that yield variant data-type cannot be used to define calculated columns."

This is how I'm computing for the Field TAT:

FieldTAT = 
    VAR vTAT = 
        IF('Audits'[AuditEndDate] = 'Audits'[ReportReceivedDate], 0, //Same day processing
            DATEDIFF('Audits'[AuditEndDate], 'Audits'[ReportReceivedDate], DAY)
        )

    RETURN IF(vTAT < -1, BLANK(), IF(vTAT = -1, 0, vTAT))

[DataNinja777]

Hi @olimilo ,

It sounds like you've run into a classic DAX challenge. The issue stems from how DAX handles BLANK() values during comparisons. In a numerical context, DAX performs a data type coercion, which means an expression like 'Audits'[FieldTAT] = 0 will evaluate to TRUE if [FieldTAT] is either 0 or BLANK(). This makes the order of your conditions in the SWITCH(TRUE()) statement absolutely critical.

Your original formula is structured correctly to avoid this common trap. By checking for blanks first, you ensure they are properly categorized as "N/A" before the numerical comparisons take place.

AuditTATCategory = 
SWITCH(
    TRUE(),
    'Audits'[FieldTAT] = BLANK(), "N/A", -- This is the correct placement
    'Audits'[FieldTAT] = 0, "On Time",
    'Audits'[FieldTAT] > 0 && 'Audits'[FieldTAT] <= 3, "On Time",
    'Audits'[FieldTAT] > 3, "Late"
)

The "variant data-type" error you see when moving the BLANK() check to the bottom is a direct symptom of the coercion problem. When the 'Audits'[FieldTAT] = 0 check comes first, it incorrectly captures all the BLANK() values, rendering your explicit BLANK() check at the end unreachable. The DAX engine flags this logical conflict as an error.

To make your formula more robust and easier to read, you can consolidate the "On Time" conditions into a single range. This approach is slightly more efficient and clearly defines the logic for that category. This revised version should resolve any lingering issues.

AuditTATCategory =
SWITCH (
    TRUE (),
    ISBLANK ( 'Audits'[FieldTAT] ), "N/A",
    'Audits'[FieldTAT] >= 0 && 'Audits'[FieldTAT] <= 3, "On Time",
    'Audits'[FieldTAT] > 3, "Late"
)

Best regards,

[GeraldGEmerick · Accepted solution]

@olimilo Try using == for your BLANK condition. That is an explicit comparison that should solve your problem. The = operator treats 0 and BLANK as the same thing.

[olimilo]

Thanks, I looked around and the ISBLANK() does the same thing. Good to learn about the difference between the = and == operators though.

https://community.fabric.microsoft.com/t5/Desktop/Switch-IF-condition-ignores-zero/td-p/1230199