Forum Discussion
Switch and True
Hello,
I am using this:
Category =
SWITCH(
TRUE(),
'Category'[lineamount] < 5000.01, "A. <5,000.01",
'Category'[lineamount] >= 5000.00 && 'Category'[lineamount] < 10000.01, "B. >5,000.00 - <10,000.01",
'Category'[lineamount] >= 10000.00 && 'Category'[lineamount] < 50000.01, "C. >10,000.00 - <50,000.01",
'Category'[lineamount] >= 50000.00, "D. >50,000.00",
"Uncategorized"
)
but its not reading it correctly. When I extracted the data from PowerBi to confirm there are amount that are not aligning. For example. See spreadsheet below, cant seem to attach the entire spreadsheet. Thank you for helping!
$39,092.61 <5,000.01
$36,001.15 <5,000.01
$27,819.80 <5,000.01
$23,626.00 <5,000.01
$22,950.02 <5,000.01
$21,411.56 <5,000.01
$19,548.57 <5,000.01
$19,500.00 <5,000.01
$18,450.00 <5,000.01
$15,749.98 <5,000.01
$13,628.40 <5,000.01
$12,727.00 <5,000.01
$12,509.60 <5,000.01
$11,611.00 <5,000.01
$10,745.00 <5,000.01
$10,246.60 <5,000.01
$10,000.00 <5,000.01
$9,998.70 <5,000.01
$9,957.60 <5,000.01
$9,709.00 <5,000.01
$9,512.52 <5,000.01
$9,323.00 <5,000.01
$9,282.42 <5,000.01
$9,127.59 <5,000.01
$9,002.59 <5,000.01
$8,690.00 <5,000.01
$8,630.02 <5,000.01
$8,583.51 <5,000.01
$8,580.00 <5,000.01
$8,529.00 <5,000.01
$7,796.65 <5,000.01
$7,507.53 <5,000.01
$7,465.00 <5,000.01
$7,309.28 <5,000.01
$7,300.00 <5,000.01
$7,223.78 <5,000.01
$7,172.00 <5,000.01
$7,167.50 <5,000.01
$7,126.00 <5,000.01
$7,006.00 <5,000.01
$6,981.25 <5,000.01
$6,894.14 <5,000.01
$6,828.30 <5,000.01
$6,792.35 <5,000.01
$6,695.52 <5,000.01
$6,620.00 <5,000.01
$6,562.78 <5,000.01
$6,552.00 <5,000.01
$6,440.00 <5,000.01
$6,433.04 <5,000.01
$6,294.98 <5,000.01
$6,260.00 <5,000.01
$6,207.50 <5,000.01
$5,950.00 <5,000.01
$5,769.39 <5,000.01
$5,625.96 <5,000.01
$5,600.00 <5,000.01
$5,578.69 <5,000.01
$5,551.65 <5,000.01
$5,530.00 <5,000.01
$5,496.17 <5,000.01
$5,460.16 <5,000.01
$5,427.84 <5,000.01
$5,400.00 <5,000.01
$5,393.20 <5,000.01
$5,355.00 <5,000.01
$5,327.66 <5,000.01
$5,238.59 <5,000.01
$5,218.00 <5,000.01
$5,158.80 <5,000.01
$5,142.52 <5,000.01
$5,047.95 <5,000.01
I hope it can work for you. Create a calculated column like that:
Category =
SWITCH(
TRUE(),
'Category'[lineamount] >= 50000.00, "D. >50,000.00",
'Category'[lineamount] >= 10000.00 && 'Category'[lineamount] < 50000.01, "C. >10,000.00 - <50,000.01",
'Category'[lineamount] >= 5000.00 && 'Category'[lineamount] < 10000.01, "B. >5,000.00 - <10,000.01",
'Category'[lineamount] < 5000.01, "A. <5,000.01",
"Uncategorized"
)
In SWITCH(TRUE()) statement, the order of conditions is critical because it evaluates sequentially.
That's why, you should evaluate the higher-value conditions first before moving to lower-value condition.
Best Regards,
Muhammad YousafIf this post helps, then please consider "Accept it as the solution" to help the other members find it more quickly.
7 Replies
looks like you created a measure. you need to create a calculated column
Category = SWITCH( TRUE(), [lineamount] < 5000.01, "A. <5,000.01", [lineamount] < 10000.01, "B. >5,000.00 - <10,000.01", [lineamount] < 50000.01, "C. >10,000.00 - <50,000.01", "D. >50,000.00" )Thank you so much for replying, I created a caculated column:
% of PO's created by value =
SWITCH(
TRUE(),
'SCM POAndItemsByProcurementCategory'[lineamount] < 5000.01, "A. <5,000.01",
'SCM POAndItemsByProcurementCategory'[lineamount] < 10000.01, "B. >5,000.00 - <10,000.01",
'SCM POAndItemsByProcurementCategory'[lineamount] < 50000.01, "C. >10,000.00 - <50,000.01",
'SCM POAndItemsByProcurementCategory'[lineamount] > 50000.01, "D. >50,000.00"
)I did change it based from what you suggested but its still showing me this:
Thank you again!
I hope it can work for you. Create a calculated column like that:
Category =
SWITCH(
TRUE(),
'Category'[lineamount] >= 50000.00, "D. >50,000.00",
'Category'[lineamount] >= 10000.00 && 'Category'[lineamount] < 50000.01, "C. >10,000.00 - <50,000.01",
'Category'[lineamount] >= 5000.00 && 'Category'[lineamount] < 10000.01, "B. >5,000.00 - <10,000.01",
'Category'[lineamount] < 5000.01, "A. <5,000.01",
"Uncategorized"
)
In SWITCH(TRUE()) statement, the order of conditions is critical because it evaluates sequentially.
That's why, you should evaluate the higher-value conditions first before moving to lower-value condition.
Best Regards,
Muhammad YousafIf this post helps, then please consider "Accept it as the solution" to help the other members find it more quickly.
Hi,
Your calculated column formula works just fine for me
I think I found the reason, I am using a line amount $ value per order, so for example there are 3 line amounts in an order, when I use the order # to count the category it will not be more than since there are duplicate order numbers.
Thank you so much for your help! appreciate it a lot!
