Nested If Statement Formula Issue

DAX DAX Calculations
1

Hi All,

I created a calculated column with multiple Nested IF statements which would list the appropriate graduation completion time % value based on the academic level and number of months leading to completion of each degree. However, there are a number of incorrect % values that are not corresponding to the appropriate academic level and completion months. For example, an Associates degree that is completed in 35 months would fall into the “150%” completion time category, but it’s showing as “Standard Time” instead. [Standard time for Associates is completion within 0 – 24 months.]

This is the formula that I have so far:

Associates Timeframe = 
IF('Completion Timeframe'[Academic Level Ranking] = "1" && 'Completion Timeframe'[Time to Completion (Months)] >= "0" && 'Completion Timeframe'[Time to Completion (Months)] <= "24", "Standard Time",
    IF('Completion Timeframe'[Academic Level Ranking] = "1" && 'Completion Timeframe'[Time to Completion (Months)] >= "25" && 'Completion Timeframe'[Time to Completion (Months)] <= "30", "125%",
        IF('Completion Timeframe'[Academic Level Ranking] = "1" && 'Completion Timeframe'[Time to Completion (Months)] >= "31" && 'Completion Timeframe'[Time to Completion (Months)] <= "36", "150%",
            IF('Completion Timeframe'[Academic Level Ranking] = "1" && 'Completion Timeframe'[Time to Completion (Months)] >= "37" && 'Completion Timeframe'[Time to Completion (Months)] <= "42", "175%",
                IF('Completion Timeframe'[Academic Level Ranking] = "1" && 'Completion Timeframe'[Time to Completion (Months)] >= "43" && 'Completion Timeframe'[Time to Completion (Months)] <= "48", "200%",
                    IF('Completion Timeframe'[Academic Level Ranking] = "1" && 'Completion Timeframe'[Time to Completion (Months)] > "48", "200% and greater", ""))))))

Please help. Thanks!

Mike

2

Certainly you should be using SWITCH for this instead of these nested IF statements.

It’s just a much cleaner way to write logic like this.

It’s hard for me to totally understand everything here, but one thing does really stick out…for some reason you have turned all your numbers in text by placing inverted commas around them.

For example “25” is now a text value. So if you’re trying to compare it to an actual number it won’t know what you’re doing.

See if that’s the problem.