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' THENIF [Category] <> 'Unallocated'THENIF [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'ENDELSEIF [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'ENDENDELSEIF [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'ENDEND Thanks in advance, Anonymous , tamerj1 , lbendlin OliT , Anonymous, PrasanthKumar - Please help.

Hi Anonymous , 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" ))

Forum Discussion

Anonymous

Not applicable

3 years ago

Solved

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,

Anonymous , tamerj1 , lbendlin OliT , Anonymous, PrasanthKumar - Please help.

Need Help

Anonymous
3 years ago
Hi Anonymous , 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"
)
)

8 Replies

Anonymous
Not applicable
3 years ago
Hi Anonymous , 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"
)
)

Anonymous

Not applicable

3 years ago

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
3 years ago
Hi Anonymous ,
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.
- Anonymous
  Not applicable
  3 years ago
  Anonymous : Hi, Just checking if you can please give me your thoughts on the above error after implementing your logic. Thanks

Forum Discussion

Need help to write Multiple IF statements in a Calculated Column

8 Replies

Related content

Multiple IF statements in DAX

Multiple nested IF statements

Using Visual Calculation on a Calculated Group with Multiple Calculated Items

DAX Calculate IF OR Statement

If Statement Calculated Column Returning Incorrectly