How to use switch function to return 3 results?

[dcheng029]

Hi team,

I am currently working with below table data columns:

- Category Type: A or B

- Calendar Days: numerical between 1 to max

- Business Days: numerical between 1 to max

- Identity 1: numerical between 0 to max

- Identity 2: numerical between 0 to max

I am trying to create a table column which follows the below logic:

[1] - if Category Type is A and Calendar Days is equal or less than 30 and Identity 1 is greater than 0, return "YES"

[2] - if Category Type is A and Calendar Days is equal or less than 45 and Identity 2 is greater than 0, return "YES"

[3] - if Category Type is B and Business Days is equal or less than 16, return "YES"

[4] - if both Identity 1 and Identity 2 is greater than 0, follow the logic of [2]

[5] - everything else that does not pass the 3 logics above return "NO"

I have tried utilising the SWITCH and IF functions to no avail, if someone can please point me in the right direction with the correct DAX expression that'd be greatly appreciated!

[Wilson_]

Hi dcheng029,

Can you also share how/where you want to see this result and/or how you want to use this calculation? Do you want to see it in a visual? Do you want to be able to use it to filter your data? etc.

[dcheng029]

Hi Wilson,

Thanks for the response.

Ideally would like the result to be in the format of a column so it can be extracted in visuals (i.e. bar graph comparing YES and NO values, and cards to show the numerical values of YES and NO entries).

Thanks again!

[Wilson_ · Accepted solution]

dcheng029,

You were on the right path with SWITCH. The SWITCH ( TRUE() ) pattern is probably the most intuitive way to work with SWITCH, especially if you have multiple criteria within your different conditions.

Try:

SWITCH (
    TRUE(),
    Table[Category Type] = "A" && Table[Calendar Days] <= 30 && Table[Identity 1] > 0, "YES",
    Table[Category Type] = "A" && Table[Calendar Days] <= 45 && Table[Identity 2] > 0, "YES",
    Table[Category Type] = "B" && Table[Business Days] <= 16, "YES",
    Table[Identity 1] > 0 && Table[Identity 2] > 0 && Table[Category Type] = "A" && Table[Calendar Days] <= 45, "YES",
    "NO"
)

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

[dcheng029]

Worked like a charm @Wilson_ thank you!

[Wilson_]

Awesome, thanks for the update. Happy to help. 😄