Forum Discussion
IF formula with another if condition does not work properly
Hello Community,
I am trying to categorize the sales by using IF formula
here is the DAX code ;
Are you writing a calculation column or measure expression? What you've written would work in a column, but should return an error as a measure. Your pic looks like a table visual, so you would need to write a measure differently. Two suggestions:
1. Write it using the SWITCH(TRUE(), ... approach. See this article - https://powerpivotpro.com/2015/03/the-diabolical-genius-of-switch-true/ . It's a better/easier way to write nested IFs.
2. First calculate your result as a variable, then use it in SWITCH()
NewMeasure = var totalsales = SUM(Orders[Sales])
return SWITCH(TRUE(), totalsales < 100, "Low", totalsales < 1000, "Medium", "High")
If this solution works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
3 Replies
- mahoneypat
Microsoft Employee
Are you writing a calculation column or measure expression? What you've written would work in a column, but should return an error as a measure. Your pic looks like a table visual, so you would need to write a measure differently. Two suggestions:
1. Write it using the SWITCH(TRUE(), ... approach. See this article - https://powerpivotpro.com/2015/03/the-diabolical-genius-of-switch-true/ . It's a better/easier way to write nested IFs.
2. First calculate your result as a variable, then use it in SWITCH()
NewMeasure = var totalsales = SUM(Orders[Sales])
return SWITCH(TRUE(), totalsales < 100, "Low", totalsales < 1000, "Medium", "High")
If this solution works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
I wrote it as a new column. Thank you mahoneypat the switch measure is working perfectly.
What I don't understand is why the nested if calculated column does not work properly. Did I miss something?
Thank you
Hi Anonymous ,
You can try this measure.
Total Sales = Sum(Order[Sales])
Sales Category =
SWITCH (
TRUE (),
[Total Sales] < 100, "Low",
[Total Sales] >= 100
&& [Total Sales] < 1000, "Medium",
"High"
)Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!