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, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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