Forum Discussion

heathernicole's avatar
heathernicole
Icon for Continued Contributor rankContinued Contributor
10 years ago
Solved

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 v...
  • asocorro's avatar
    asocorro
    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)
    )

     

     

  • asocorro's avatar
    asocorro
    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.

     

     

  • heathernicole's avatar
    heathernicole
    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!!

  • asocorro's avatar
    asocorro
    10 years ago

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

asocorro's avatar
asocorro
Icon for Skilled Sharer rankSkilled Sharer
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)
)

 

 

  • Pellegrino's avatar
    Pellegrino
    Frequent Visitor
    3 years ago

    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")
  • heathernicole's avatar
    heathernicole
    Icon for Continued Contributor rankContinued Contributor
    10 years ago

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

    • asocorro's avatar
      asocorro
      Icon for Skilled Sharer rankSkilled Sharer
      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.

       

       

      • heathernicole's avatar
        heathernicole
        Icon for Continued Contributor rankContinued Contributor
        10 years ago

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

         

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

  • ksubramaniyam41's avatar
    ksubramaniyam41
    Frequent Visitor
    4 years ago

    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"
    )
    • WulffJoergen's avatar
      WulffJoergen
      Icon for Helper I rankHelper I
      3 years ago

      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.