Forum Discussion
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!
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.
18 Replies
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)
)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")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:
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...
I copied exactly what you posted and it worked for me. Make sure your data is of type Decimal and not Text.
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[Business UAT Start Date])) && New_Scope[Business UAT End Date]=BLANK(),"#d74e26",NOT(ISBLANK(New_Scope[Estimated Go Live])) && New_Scope[Actual Go Live]=BLANK(),"#d74e26")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.
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/APWhsxg7NQUSWITCH ( 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
