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

[sjinsd]

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!

[kingrob01]

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

[v-piga-msft · Accepted solution]

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]

Thank you Cherry! This works!