Forum Discussion

heathernicole's avatar
heathernicole
Continued 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
Skilled Sharer
10 years ago

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

greggyb's avatar
greggyb
Resident Rockstar
10 years ago

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>
)
  • greggyb's avatar
    greggyb
    Resident Rockstar
    10 years ago

    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.

  • PowerBINewbie_1's avatar
    PowerBINewbie_1
    Helper I
    8 years ago

    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.

  • asocorro's avatar
    asocorro
    Skilled Sharer
    10 years ago

    Nice!

  • Sunnie's avatar
    Sunnie
    Helper I
    6 years ago

    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.