Forum Discussion
DAX Formula issue
- Anonymous7 years ago
Probably better to use the SWITCH, TRUE() pattern for that many ifs. Give the following a try and see if that work:
SWITCH( TRUE(), [MEASURE] > 90, "Greater Than 90 Days", AND([Measure] < 90, [Measure] > 60), "Greater Than 60 Days", AND([Measure] < 60, [Measure] > 30), "Greater Than 30 Days", AND([Measure] < 30, [Measure] > 7), "Greater Than 7 Days", [Measure] <= 7,"Less Than 7 Days", 0 )
- 7 years ago
Looks like you may be making it too complicated. Try using SWITCH() instead and simplify the conditions:
Test Column = SWITCH ( TRUE (), DaysSince[Days since Financial Statement] > 90, "Greater than 90 days", DaysSince[Days since Financial Statement] > 60, "Greater than 60 days", DaysSince[Days since Financial Statement] > 30, "Greater than 30 days", DaysSince[Days since Financial Statement] > 7, "Greater than 7 days", DaysSince[Days since Financial Statement] <= 7, "Less than 7 days", -1 )Since SWITCH() will end evaluation once it hits a true, your code should be able to be simplified as above. It's possible in all of the nesting, etc you introduced an odd character somewhere in one of the values.
Also note that in the case of getting to the ELSE part of the switch (-1), this will still be a text field, not a number.
Hope this helps
David
Hi Chris,
Thank you for responding.
You are right AND() takes in only 2 arguments. I modified my code and tried using both 'AND' and '&&'.
But I am getting an error that states: "Expressions that yield variant data-type cannot be used to define calculated columns."
Here's what my a part of my new code looks like:
Also, here's my code for 'AND':
IF(
AND(DaysSince[Days since Financial Statement] > 90, DaysSince[Days since Financial Statement] > 60), "Greater than 90days",
IF(
AND(DaysSince[Days since Financial Statement] < 90, DaysSince[Days since Financial Statement] > 60), "Greater than 60days",
IF(
AND(DaysSince[Days since Financial Statement] < 60, DaysSince[Days since Financial Statement] > 30), "Greater than 30days",
IF(
AND(DaysSince[Days since Financial Statement] < 30, DaysSince[Days since Financial Statement] > 7), "Greater than 7days",
IF(
DaysSince[Days since Financial Statement] <=7, "Less than 7 days",-1)
)
)
)
)
- Anonymous7 years agoNot applicable
Probably better to use the SWITCH, TRUE() pattern for that many ifs. Give the following a try and see if that work:
SWITCH( TRUE(), [MEASURE] > 90, "Greater Than 90 Days", AND([Measure] < 90, [Measure] > 60), "Greater Than 60 Days", AND([Measure] < 60, [Measure] > 30), "Greater Than 30 Days", AND([Measure] < 30, [Measure] > 7), "Greater Than 7 Days", [Measure] <= 7,"Less Than 7 Days", 0 )
- Anonymous7 years agoNot applicable
Yes it works!
Thank you! :)
- dedelman_clng7 years agoCommunity Champion
Looks like you may be making it too complicated. Try using SWITCH() instead and simplify the conditions:
Test Column = SWITCH ( TRUE (), DaysSince[Days since Financial Statement] > 90, "Greater than 90 days", DaysSince[Days since Financial Statement] > 60, "Greater than 60 days", DaysSince[Days since Financial Statement] > 30, "Greater than 30 days", DaysSince[Days since Financial Statement] > 7, "Greater than 7 days", DaysSince[Days since Financial Statement] <= 7, "Less than 7 days", -1 )Since SWITCH() will end evaluation once it hits a true, your code should be able to be simplified as above. It's possible in all of the nesting, etc you introduced an odd character somewhere in one of the values.
Also note that in the case of getting to the ELSE part of the switch (-1), this will still be a text field, not a number.
Hope this helps
David
- Anonymous7 years agoNot applicable
Thank you for this solution!
- Anonymous7 years agoNot applicable
General rule I like to follow is that if it's more than one IF, use SWITCH. So much easier to use and debug :smileyhappy: