Blank handling in banding

[vanessafvg]

i have this switch statement below

hoever when i have blank values it groups it into the 0-4 weeks, is blank seen as 0 and why is it doing this?   sure blank shoudl be ignored by the banding? or all into n/a?

Weeks since last seen by GP/NMP Banding =
SWITCH (
TRUE (),
AND (
Substance[Weeks since last seen by GP/NMP] >= 0,
Substance[Weeks since last seen by GP/NMP] <= 4
), "0-4 weeks",
AND (
Substance[Weeks since last seen by GP/NMP] > 4,
Substance[Weeks since last seen by GP/NMP] <= 8
), "4-8 weeks",
AND (
Substance[Weeks since last seen by GP/NMP] > 8,
Substance[Weeks since last seen by GP/NMP] <= 12
), "8-12 weeks",
Substance[Weeks since last seen by GP/NMP]> 12,
"12+ weeks",
"n/a"
)

[v-yulgu-msft]

Hi @vanessafvg,

Agree with AlbertoFerrari's suggestion, I made some adjustment in original formula:

Weeks since last seen by GP/NMP Banding 2 =
IF (
    ISBLANK ( Substance[Weeks since last seen by GP/NMP] ),
    "n/a",
    SWITCH (
        TRUE (),
        AND (
            Substance[Weeks since last seen by GP/NMP] >= 0,
            Substance[Weeks since last seen by GP/NMP] <= 4
        ), "0-4 weeks",
        AND (
            Substance[Weeks since last seen by GP/NMP] > 4,
            Substance[Weeks since last seen by GP/NMP] <= 8
        ), "4-8 weeks",
        AND (
            Substance[Weeks since last seen by GP/NMP] > 8,
            Substance[Weeks since last seen by GP/NMP] <= 12
        ), "8-12 weeks",
        Substance[Weeks since last seen by GP/NMP] > 12, "12+ weeks",
        "n/a"
    )
)

Best regards,
Yuliana Gu

[AlbertoFerrari · Accepted solution]

In DAX, BLANK equals zero.

If you want a special treatment, add an ISBLANK ( yourcolumn ) as the first test, so to avoid further processing of the conditions.
Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

[vanessafvg]

@AlbertoFerrari grazie!