Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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.
Solved! Go to 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
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
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.
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.
same change to double quotation marks
"0-30days"
Proud to be a Super User!
Thanks !
pls try double quotation marks instead.
"Future Offboard"
Proud to be a Super User!
@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
@v-rzhou-msft : Hi, Just checking if you can please give me your thoughts on the above error after implementing your logic. Thanks
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
84 | |
84 | |
73 | |
49 |
User | Count |
---|---|
143 | |
132 | |
110 | |
64 | |
55 |