Forum Discussion

sjinsd's avatar
sjinsd
Frequent Visitor
8 years ago
Solved

Help with SWITCH ( TRUE() with multiple items to evaluate

Hi! I am trying to evaluate eligibility based on the value of several combinations in each record.  I think I will have to use nested SWITCH functions, but I can't even get past the first one. When I do this in Excel, I use filtering and multiple columns to evaluate eligibility.

 

Here's one case:

If the membership type is REG A, then they are eligible if:

  • They are 70 or over
  • They have 30 years of service or more
  • or if they have a combination of age greater than or equal to 50 and 10 or more years of service.

The other cases are similar with different age and service requirements.

 

Column = SWITCH(TRUE()
    [Membership Type] = "REG",
       [Age] >= 70, "Eligible",
        [Total Years Service] >=30, "Eligible",
        [Age] >50 && [Total Years Service] > 10, "Eligible",

"Not")

 

Am I on the right track? Thank you in advance for your help!

 

  • v-piga-msft's avatar
    v-piga-msft
    8 years ago

    Hi sjinsd,

     

    By my test with your dax expression, I get an error, do you have errors when you use the formula?

     

     

    I suggest you could add IF function to determin the type of membership at first. You could have a reference of the formula below.

     

    Column =
IF (
    'Table1'[menbership] = "Type",
    SWITCH (
        TRUE (),
        [Age] >= 70, "Eligible",
        [Service] >= 30, "Eligible",
        [Age] > 50
            && [Service] > 10, "Eligible",
        "Not"
    ),
    "NOt"
)

    The result is below.

     

    You could modify the formula above according to your requirement.

     

    Best Regards,

    Cherry

3 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    8 years ago

    Hi sjinsd,

     

    By my test with your dax expression, I get an error, do you have errors when you use the formula?

     

     

    I suggest you could add IF function to determin the type of membership at first. You could have a reference of the formula below.

     

    Column =
IF (
    'Table1'[menbership] = "Type",
    SWITCH (
        TRUE (),
        [Age] >= 70, "Eligible",
        [Service] >= 30, "Eligible",
        [Age] > 50
            && [Service] > 10, "Eligible",
        "Not"
    ),
    "NOt"
)

    The result is below.

     

    You could modify the formula above according to your requirement.

     

    Best Regards,

    Cherry

    • sjinsd's avatar
      sjinsd
      Frequent Visitor
      8 years ago

      Thank you Cherry! This works! :smileyhappy:

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Help with trying to create a column that assigns a value based on following Table.  Getting DAX error.  If I reference AI table and location is x then value is value. 

    AI Table       Quest          New Ideal Column 
    AI Record    Location       Weight Value

     

    Weight = SWITCH ( TRUE(),
    'AI'[quest] = "California", 6,
    'AI'[quest] = "Nebraska", 3,
    'AI'[quest] = "Florida", 10,
    'AI'[quest] = "Texas", 8,
    'AI'[quest] = "Georgia",3,
    'AI'[quest] = "Newyork", 10,
    'AI'[quest] = "Maryland", 4,
    'AI'[quest] = "Ohio", 8,
    'AI'[quest] = "Alabama", 10,
    )