Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
sjinsd
Frequent Visitor

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!

 

1 ACCEPTED SOLUTION
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @sjinsd,

 

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

 

error.PNG

 

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.

Capture.PNG

 

You could modify the formula above according to your requirement.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
kingrob01
Frequent Visitor

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
Resident Rockstar
Resident Rockstar

Hi @sjinsd,

 

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

 

error.PNG

 

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.

Capture.PNG

 

You could modify the formula above according to your requirement.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you Cherry! This works! Smiley Happy

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.