cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

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

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.
3 REPLIES 3
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] = "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,
)

Resident Rockstar

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

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

Thank you Cherry! This works!

Announcements

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Power BI Monthly Update - May 2024

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

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors