Using SWITCH with calculated column containing errors

[cathoms]

Hi. I'm trying to create flagging logic for procedure code changes between "OriginalProcedure" and "CurrentProcedure". Subtracting CurrentProcedure from OriginalProcedure produces patterns that can be used to indicate whether a change was a down code, up code, or neutral.

I am using a dataset created by someone else and do not have the ability to alter the query so I first created a new column by subtracting current from procedure, as follows:

CodingChange = UclFact[OriginalProcedureFinal] - UclFact[CurrentProcedureFinal] + 0

The problem is, there are a few codes that are alphanumeric rather than purely numeric, so I know it is going to create errors. I thought that would be fine because those situations are rare and errors can just be flagged as 'indeterminant' or similar. When I try to visualize this column I get the following error message:

I want to flag each row as an up code, down code, or neutral change, so I tried creating another new column with SWITCH, as follows:

CodeChangeFlag = 
SWITCH (
    TRUE (),
    UclFact[CodingChange] = -1, "Up Coded",
    UclFact[CodingChange] = -2, "Up Coded",
    UclFact[CodingChange] = -11, "Up Coded",
    UclFact[CodingChange] = -12, "Up Coded",
    UclFact[CodingChange] = 9, "Up Coded",
    UclFact[CodingChange] = 1, "Down Coded",
    UclFact[CodingChange] = 11, "Down Coded",
    UclFact[CodingChange] = -10, "Neutral",
    UclFact[CodingChange] = 10, "Neutral",
    UclFact[CodingChange] < -99, "Neutral",
    UclFact[CodingChange] > 99, "Neutral",
    IFERROR(UclFact[CodingChange],0),"Uncertain"
)

but when I try to visualize CodeChangeFlag I get another error message:

How can I account for this problem with mixed data types in the same column without modifying the query itself? Or, is there some other, completely different way I might go about this?

Thank you in advance!

[cathoms · Accepted solution]

I couldn't take the approach I had planned because my connection is Direct Query, which doesn't allow the use of ISERROR in calculated columns. Instead, I modifed the "CodingChange" column to eliminate errors. Then, in this column I just used SWITCH without the ISERROR statement as shown in my original post.

[Fowmy]

@cathoms 

The issue in the formula is the last line with IFERROR returning True/False. Try the following formula

CodeChangeFlag =
IF (
    ISERROR ( UclFact[CodingChange] ),
    "Uncertain",
    SWITCH (
        TRUE (),
        UclFact[CodingChange] = -1, "Up Coded",
        UclFact[CodingChange] = -2, "Up Coded",
        UclFact[CodingChange] = -11, "Up Coded",
        UclFact[CodingChange] = -12, "Up Coded",
        UclFact[CodingChange] = 9, "Up Coded",
        UclFact[CodingChange] = 1, "Down Coded",
        UclFact[CodingChange] = 11, "Down Coded",
        UclFact[CodingChange] = -10, "Neutral",
        UclFact[CodingChange] = 10, "Neutral",
        UclFact[CodingChange] < -99, "Neutral",
        UclFact[CodingChange] > 99, "Neutral"
    )
)

[cathoms]

Strangely, Power BI is telling me ISERROR doesn't exist and is not a function...

Going to post a seperate question about this...

[cathoms]

Just looked it up and "This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules."

I'm trying to create a calculated column in DirectQuery mode.

So, your solution won't work, unfortunately... Other ideas?