Forum Discussion
SWITCH statement in DAX using a "between this value and this value" as a filter?
- 10 years ago
The SWITCH statement allows comparisons with constants only. You need to use an IF statement. For a calculated column, this is an example:
Pop Classification =
IF(Population[Pop] >= 1000 && Population[Pop] <= 25000,
1,
IF(Population[Pop] >= 25001 && Population[Pop] <= 50000,
2,
0)
) - 10 years ago
I copied exactly what you posted and it worked for me. Make sure your data is of type Decimal and not Text.
- 10 years ago
Figured it out... or at least partially... it's summarizing data when it shouldn't so it's adding it somewhere.
So all of your suggestions have helped! :) Thank you!!
- 10 years ago
I was about to tell you that. Check the table fields for an aggregate function.
SWITCH() can only compare to a constant, this is true, but you can still utilize it to avoid nested IF()s for a cleaner syntax and easier modification.
Simply match against the constant TRUE(), then each of your tests (which are Boolean expressions anyway) are tested for equality with TRUE():
// DAX
// SWITCH() instead of nested IF() - works in measure, column, or table
SwitchTrue =
SWITCH(
TRUE()
,<boolean expression>, <result if true>
,<boolean expression>, <result if true>
, ..., ...
,<else condition - no trues above>
)Nice!
- greggyb10 years agoResident Rockstar
Blow someone's mind:
// DAX // Works anywhere // Don't do this without a good reason SWITCH( FALSE() ,<boolean>, <result if boolean is false) .... ,<result if all boolean expressions above are false> )Then you're testing each expression for equality with FALSE(). So tests that fail get their result evaluated.
- TealCanady10 years agoAdvocate II
A few reference articles for this method in case you are interested.
DAX making the case for switch
The diabolical genius of switch-true
Teal