Need help to write Multiple IF statements in a Calculated Column

[Anonymous]

Hi All,

New Bee here,

Can you please help me to create a column in Power BI desktop for the below logic which was created in Tableau.

I would be using this column (Ageing Category) for creating other columns.

Ageing Category=

IF [Category] <> ' Future Offboard' THEN

IF [Category] <> 'Unallocated'
THEN
IF [Delivery Aging] <= 30 THEN '0-30 Days'
ELSEIF [Delivery Aging] > 30 AND [Delivery Aging] <= 60 THEN '31-60 Days'
ELSEIF [Delivery Aging] > 60 AND [Delivery Aging] <= 90 THEN '61-90 Days'
ELSEIF [Delivery Aging] > 90 AND [Delivery Aging] <=180 THEN '91-180 Days'
ELSE '>180 Days'
END
ELSE
IF [unallocated_aging] <= 30 THEN '0-30 Days'
ELSEIF [unallocated_aging] > 30 AND [unallocated_aging] <= 60 THEN '31-60 Days'
ELSEIF [unallocated_aging] > 60 AND [unallocated_aging] <= 90 THEN '61-90 Days'
ELSEIF [unallocated_aging] > 90 AND [unallocated_aging] <=180 THEN '91-180 Days'
ELSE '>180 Days'
END
END

ELSE

IF [No_of_days] <= 30 THEN '0-30 Days'
ELSEIF [No_of_days] > 30 AND [No_of_days] <= 60 THEN '31-60 Days'
ELSEIF [No_of_days] > 60 AND [No_of_days] <= 90 THEN '61-90 Days'
ELSEIF [No_of_days] > 90 AND [No_of_days] <=180 THEN '91-180 Days'
ELSE '>180 Days'
END

END

Thanks in advance,

@v-yiruan-msft , @tamerj1 , @lbendlin @OliT , @Anonymous, @PrasanthKumar   - Please help.

[Anonymous · Accepted solution]

Hi @v-rzhou-msft , @ryan_mayu ,

I was able to get the below logic(no syntax issues)  in powerBI for the above logic but how can I include THEN, END, ELSE in my logic below (as these logics are very important to be included to get the exact output). 

Please help

Ageing Category =

IF (

    [Category] <> "Future Offboard",

    IF (

        [Category] <> "Unallocated",

        SWITCH (

            TRUE (),

            CustomSQL_BenchReport[Delivery_Aging] <= 30, "0-30 Days",

              CustomSQL_BenchReport[Delivery_Aging]>30 && CustomSQL_BenchReport[Delivery_Aging] <= 60, "31-60 Days",

             CustomSQL_BenchReport[Delivery_Aging] >60 &&  CustomSQL_BenchReport[Delivery_Aging] <= 90, "61-90 Days",

             CustomSQL_BenchReport[Delivery_Aging] >90  && CustomSQL_BenchReport[Delivery_Aging] <= 180, "91-180 Days",

            CustomSQL_BenchReport[Delivery_Aging] > 180, ">180 Days"

        ),

        SWITCH (

            TRUE (),

            CustomSQL_BenchReport[unallocated_aging] <= 30, "0-30 Days",

            CustomSQL_BenchReport[unallocated_aging] >30 && CustomSQL_BenchReport[unallocated_aging] <= 60, "31-60 Days",

           CustomSQL_BenchReport[unallocated_aging] >60  && CustomSQL_BenchReport[unallocated_aging] <= 90, "61-90 Days",

            CustomSQL_BenchReport[unallocated_aging] >90  && CustomSQL_BenchReport[unallocated_aging] <= 180, "91-180 Days",

            CustomSQL_BenchReport[unallocated_aging] > 180, ">180 Days"

        )

    ),

    SWITCH (

        TRUE (),

        CustomSQL_BenchReport[No_of_days] <= 30, "0-30 Days",

         CustomSQL_BenchReport[No_of_days]  >30 && CustomSQL_BenchReport[No_of_days] <= 60, "31-60 Days",

        CustomSQL_BenchReport[No_of_days] >60  && CustomSQL_BenchReport[No_of_days] <= 90, "61-90 Days",

         CustomSQL_BenchReport[No_of_days] >90  && CustomSQL_BenchReport[No_of_days] <= 180, "91-180 Days",

        CustomSQL_BenchReport[No_of_days] >180, ">180 Days"

    )

)

[v-rzhou-msft]

Hi @Anonymous ,

You can try this code to create a calculated column.

Ageing Category =
IF (
    [Category] <> ' Future Offboard',
    IF (
        [Category] <> 'Unallocated',
        SWITCH (
            TRUE (),
            [Delivery Aging] <= 30, '0-30 Days',
            [Delivery Aging] <= 60, '31-60 Days',
            [Delivery Aging] <= 90, '61-90 Days',
            [Delivery Aging] <= 180, '91-180 Days',
            '>180 Days'
        ),
        SWITCH (
            TRUE (),
            [unallocated_aging] <= 30, '0-30 Days',
            [unallocated_aging] <= 60, '31-60 Days',
            [unallocated_aging] <= 90, '61-90 Days',
            [unallocated_aging] <= 180, '91-180 Days',
            '>180 Days'
        )
    ),
    SWITCH (
        TRUE (),
        [No_of_days] <= 30, '0-30 Days',
        [No_of_days] <= 60, '31-60 Days',
        [No_of_days] <= 90, '61-90 Days',
        [No_of_days] <= 180, '91-180 Days',
        '>180 Days'
    )
)

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-rzhou-msft ,

Thanks for replying to my query.

I tried to replicate your logic in the Power BI column, But I get the below error:

"Cannot find table "furture Offboard"  . Where as 'Future Offboard" is one of the value in the CATEGORY column  . Please help.

[ryan_mayu]

same change to double quotation marks

"0-30days"

[Anonymous]

Thanks !

[ryan_mayu]

pls try double quotation marks instead. 

"Future Offboard"

[Anonymous]

@ryan_mayu  , I tried it worked but now i got the below error:

Delivery Ageing is a column and "0-30 days" is a bucket

[Anonymous]

@v-rzhou-msft : Hi, Just checking if you can please give me your thoughts on the above error after implementing your logic. Thanks