Reply
Member
Posts: 175
Registered: ‎02-05-2016
Accepted Solution

SWITCH statement in DAX using a "between this value and this value" as a filter?

VERY new to DAX - I think my issue is I haven't nailed down how to ask the right question yet - which hinders research for an answer:

 

Trying to create an IF (or a Switch) statement

 

If "this value" in column is BETWEEN 9999 AND 24999 THEN, (2000)

 

I've already tried writing it using greater than or equal to 10,000 and less than or equal to 24,999 - that didn't work. 

 

I've looked at several switch statement examples but they all use a "If this value = "this" set up and I'm looking for a range.

 

Any pointers or sites that be helpful in figuring out the proper syntax?


THANKS!

~heathernicoale

Accepted Solutions
Member
Posts: 116
Registered: ‎09-09-2015

Re: SWITCH 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)
)

 

 

Connect with me in LinkedIn: https://pr.linkedin.com/in/adolfosocorro
Follow me on Twitter: https://twitter.com/AdolfoSocorro

View solution in original post

Member
Posts: 116
Registered: ‎09-09-2015

Re: SWITCH statement in DAX using a "between this value and this value" as a filter?

I copied exactly what you posted and it worked for me.  Make sure your data is of type Decimal and not Text.

 

 

2016-02-18_9-55-39.jpg

Connect with me in LinkedIn: https://pr.linkedin.com/in/adolfosocorro
Follow me on Twitter: https://twitter.com/AdolfoSocorro

View solution in original post

Member
Posts: 175
Registered: ‎02-05-2016

Re: SWITCH statement in DAX using a "between this value and this value" as a filter?

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! Smiley Happy Thank you!!

~heathernicoale

View solution in original post

Member
Posts: 116
Registered: ‎09-09-2015

Re: SWITCH statement in DAX using a "between this value and this value" as a filter?

I was about to tell you that.  Check the table fields for an aggregate function.

Connect with me in LinkedIn: https://pr.linkedin.com/in/adolfosocorro
Follow me on Twitter: https://twitter.com/AdolfoSocorro

View solution in original post


All Replies
Member
Posts: 116
Registered: ‎09-09-2015

Re: SWITCH 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)
)

 

 

Connect with me in LinkedIn: https://pr.linkedin.com/in/adolfosocorro
Follow me on Twitter: https://twitter.com/AdolfoSocorro
Member
Posts: 175
Registered: ‎02-05-2016

Re: SWITCH statement in DAX using a "between this value and this value" as a filter?

This 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&colon;

 

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...

~heathernicoale
Member
Posts: 116
Registered: ‎09-09-2015

Re: SWITCH statement in DAX using a "between this value and this value" as a filter?

I copied exactly what you posted and it worked for me.  Make sure your data is of type Decimal and not Text.

 

 

2016-02-18_9-55-39.jpg

Connect with me in LinkedIn: https://pr.linkedin.com/in/adolfosocorro
Follow me on Twitter: https://twitter.com/AdolfoSocorro
Member
Posts: 175
Registered: ‎02-05-2016

Re: SWITCH statement in DAX using a "between this value and this value" as a filter?

Hmm.... I'm glad it's working.. .at least for someone. Smiley Happy 

 

Here's what it's doing to mine... pbi.PNG

~heathernicoale
Member
Posts: 175
Registered: ‎02-05-2016

Re: SWITCH statement in DAX using a "between this value and this value" as a filter?

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! Smiley Happy Thank you!!

~heathernicoale
Member
Posts: 116
Registered: ‎09-09-2015

Re: SWITCH statement in DAX using a "between this value and this value" as a filter?

I was about to tell you that.  Check the table fields for an aggregate function.

Connect with me in LinkedIn: https://pr.linkedin.com/in/adolfosocorro
Follow me on Twitter: https://twitter.com/AdolfoSocorro
Highlighted
New Contributor
Posts: 545
Registered: ‎10-27-2015

Re: SWITCH statement in DAX using a "between this value and this value" as a filter?

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>
)
Member
Posts: 116
Registered: ‎09-09-2015

Re: SWITCH statement in DAX using a "between this value and this value" as a filter?

Nice!

Connect with me in LinkedIn: https://pr.linkedin.com/in/adolfosocorro
Follow me on Twitter: https://twitter.com/AdolfoSocorro
New Contributor
Posts: 545
Registered: ‎10-27-2015

Re: SWITCH statement in DAX using a "between this value and this value" as a filter?

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.