Forum Discussion
Help improving nested IF statements
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" )
)
)
)
)
)
)
)
)
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"
)
7 Replies
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"
)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
You need to create it as a calculated column, not a measure
Reference: https://docs.microsoft.com/en-us/dax/switch-function-dax
has used switch in the calculated column, FYI.
