cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Continued Contributor

## 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
4 ACCEPTED SOLUTIONS
Skilled Sharer

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

Skilled Sharer

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

Continued Contributor

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

~heathernicoale
Skilled Sharer

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

18 REPLIES 18
Anonymous
Not applicable

SWITCH ( TRUE(), NOT(ISBLANK(Sales[Profile Percentage])) && ISBLANK(Sales[Employee Percentage]), Sales[principal sales Qty]*Sales[Profile Percentage], NOT(ISBLANK(Sales[Employee Percentage])) && ISBLANK(Sales[Profile Percentage]), Sales[principal sales Qty]*Sales[Employee Percentage], ISBLANK(Sales[Profile Percentage]) && ISBLANK(Sales[Employee Percentage]), Sales[principal sales Qty], Sales[Employee Percentage]=0 && Sales[Profile Percentage]<>0 ,Sales[principal sales Qty]*Sales[Profile Percentage], Sales[principal sales Qty]*Sales[Employee Percentage]*Sales[Profile Percentage] )

This is the logic... Can anyone help

New Member

# Using Switch statement in PowerBI DAX to split Range of Numbers

If you want to split range of Numbers into same length parts and labeling them or classifying them using Switch statement, your solution is here.
Switch statement don't support between and it just works with constant values.
Here I mentioned a simple mathematical trick to split ranges of numbers into same length parts and label them using Dax Switch statement.

you can watch my video here:
https://youtu.be/APWhsxg7NQU

Skilled Sharer

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

Frequent Visitor

This is no longer True (if it ever was) , using Switch can be used for ranges and not just descrete values.

Thus:

Total Amt Groups Using Switch = SWITCH(TRUE,Transactions[Total Amt]<2000,"Low",Transactions[Total Amt]>2000 && Transactions[Total Amt]<5000,"Medium","High")
Frequent Visitor

I have same senario the below Column measure is not working. Kindly please help me on this

Phase_Highlight = SWITCH(TRUE(),
NOT(ISBLANK(New_Scope[Requirement Start Date])) && New_Scope[Requirement End Date]= BLANK(),"#d74e26",
NOT(ISBLANK(New_Scope[Analysis Start Date])) && New_Scope[Analysis End Date]=BLANK(),"#d74e26",
NOT(ISBLANK(New_Scope[Design Start Date])) && New_Scope[Design End Date]=BLANK(),"#d74e26",
NOT(ISBLANK(New_Scope[Development Start Date])) && New_Scope[Development End Date]=BLANK(),"#d74e26",
NOT(ISBLANK(New_Scope[Estimated Go Live])) && New_Scope[Actual Go Live]=BLANK(),"#d74e26"
)
Frequent Visitor

I know it is a while ago you posted this, but a good advice is to post in separate post - I get most replies this way.

Continued Contributor

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
Skilled Sharer

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

Helper II

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.

Continued Contributor

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

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

~heathernicoale
Continued Contributor

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

~heathernicoale
Skilled Sharer

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

Resident Rockstar

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>
)```
Helper I

Hi,

Does this work with dates? For example:

SWITCH(

TRUE()

,[SharePointColumnA]="",0

,[SharePointColumnD]="", CALCULATE(SUM(DateTable[WORKDAY], DATESBETWEEN (DateTable[Date], 'SharePointList'[SharePoint ColumnA], Format(TODAY(), "mm/dd/yyyy")

CALCULATE(SUM(DateTable[WORKDAY], DATESBETWEEN (DateTable[Date], 'SharePointList'[SharePoint ColumnA],'SharePointList'[SharePoint ColumnD]))

I'm simply trying to find the networkdays/business days between two dates when the end user supplies those two dates. Otherwise, if they don't supply the beginning date which is the SharePoint Column A date, then set the value in that row to 0. and if they only supply an end date, then take the date that was supplied in the beginning date and give me the total value of the business days between that date and today.

Helper I

How would I use SWITCH to split a column at the first letter , so that I have two new columns with just the first letter.

Column1 = A, B, C to M...

Column2 = N, O, P to Z...

I'm trying to have my vizual filter by first letter of the row.

Skilled Sharer

Nice!

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

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

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors