Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

Need help to write Multiple IF statements in a Calculated Column

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.

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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"
    )
)

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

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
Community Support
Community Support

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
Not applicable

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.

 

naveen121_0-1666850525300.png

 

same change to double quotation marks

"0-30days"





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Thanks !

pls try double quotation marks instead. 

"Future Offboard"





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

@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

 

naveen121_0-1667193071067.png

 

Anonymous
Not applicable

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.