Forum Discussion
Continued ContributorSWITCH statement in DAX using a "between this value and this value" as a filter?
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)
)I copied exactly what you posted and it worked for me. Make sure your data is of type Decimal and not Text.
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!!
I was about to tell you that. Check the table fields for an aggregate function.
Continued ContributorThis looks right to me - I've been working on it and researching while waiting... here's what I've got so far:
Bonus Points =
IF('SALES DETAILS'[Sales Line Applied Amount] >= 10000 && 'SALES DETAILS'[Sales Line Applied Amount] <= 24999,
2000,
IF('SALES DETAILS'[Sales Line Applied Amount] >= 25000 && 'SALES DETAILS'[Sales Line Applied Amount] <= 49999,
4000, IF('SALES DETAILS'[Sales Line Applied Amount] >= 50000,
6000,
0)
))
This is what I'm trying to accomplish - but it's not working properly. It gives a value - but it's not giving the right value for some reason... :/
Here's some example data:
Sales Line Applied Amount
163.00
11,000.45
28.00
25,000.35
For example the bonus points applied should be 2000 pts for the 11,000.45 line item
and 4000 pts for the 25,000.35 line item... but it's not doing that for some reason...
Skilled SharerI copied exactly what you posted and it worked for me. Make sure your data is of type Decimal and not Text.
- heathernicole
Hmm.... I'm glad it's working.. .at least for someone. :)
Here's what it's doing to mine...
- heathernicole
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!!
- asocorro
I was about to tell you that. Check the table fields for an aggregate function.
Hi Ascorro,
Quick question, what if i want to create a measure with the decimal values in a column using the same switch scenario as given. How can it be acheieved? Please guide me on this.