Help improving nested IF statements

[cathoms]

Hi. In the dataset I am working with there is a numeric column, CancellationLeadTimeInDays, for how many days between an appointment and when it was cancelled. The values range from -26 (negative values are after the appointment) to 400+

This is a numeric column, not a measure, so I can't use SWITCH. I'm looking for a more elegant solution than my nested if statements. Here is what I have:

Cancellation Notice Time = 
IF (
    Visits[CancellationLeadTimeInDays] >= 92, "More than 3 months",
    IF (
        Visits[CancellationLeadTimeInDays] >= 61, "2-3 months",
        IF (
            Visits[CancellationLeadTimeInDays] > 30, "1-2 months",
            IF (
                Visits[CancellationLeadTimeInDays] > 14, "3-4 weeks",
                IF (
                    Visits[CancellationLeadTimeInDays] > 7, "1-2 weeks",
                    IF (
                        Visits[CancellationLeadTimeInDays] > 2, "Within 1 week",
                        IF (
                            Visits[CancellationLeadTimeInDays] > 1, "Within 48 hrs",
                            IF (
                                Visits[CancellationLeadTimeInDays] >= 0, "Within 24 hrs",
                                IF ( 
                                    Visits[CancellationLeadTimeInDays] < 0, "After appointment" )
                            )
                        )
                    )
                )
            )
        )
    )
)

[sevenhills]

Reference: https://docs.microsoft.com/en-us/dax/switch-function-dax 

has used switch in the calculated column, FYI.

[blopez11 · Accepted solution]

I think you can use a switch

Cancellation Notice Time Buckets =
SWITCH (
TRUE (),
[CancellationLeadTimeInDays] >= 92, "More than 3 months",
[CancellationLeadTimeInDays] >= 61, "2-3 months",
[CancellationLeadTimeInDays] > 30, "1-2 months",
[CancellationLeadTimeInDays] > 14, "3-4 weeks",
[CancellationLeadTimeInDays] > 7, "1-2 weeks",
[CancellationLeadTimeInDays] > 2, "Within 1 week",
[CancellationLeadTimeInDays] > 1, "Within 48 hours",
[CancellationLeadTimeInDays] >= 0, "Within 24 hours",
"After appointment"
)

[cathoms]

That doesn't work. Instead, I get the following error message: "The value for 'CancellationLeadTimeInDays' cannot be determined. Either the column doesn't exist, or there is no current row for this column."

The column definitely exists

[blopez11]

You need to create it as a calculated column, not a measure

[cathoms]

It is not a measure. Right now CancellationLeadTimeInDays is pulled directly from our datamart in Caboodle as a numeric column in the dataset. I don't control the dataset, so I'm guessing I would need to replicate this column somehow to make it either a measure or a calculated column...

Any ideas? Thanks for your help!

[blopez11]

Sorry, I meant, create "Cancellation Notice Time Buckets" as a calculated column

[cathoms]

Ah. I still have a lot to learn...

That worked, thanks much!