topic Re: Need DAX to format Yes or No question returning one answer. in DAX Commands and Tips

Need DAX to format Yes or No question returning one answer.

dataanalyst2023 — Wed, 11 Oct 2023 13:34:24 GMT

Hello,

I'm having some trouble finding the correct formula to capture the Performance mode in the same column. I'm creating a Dasboard where depends if the person asnwers Yes or No it will determine the Performance Mode. For example:

1. Does personnel know where to find instructions? = No and Does personnel perform this task more than 10 times a day? = Yes is Skill-Based Mode

2. Does personnel perform this task more than 10 times a day? = Yes is a Skill-Based Mode

3. Did personnel demonstrate proper use of PPE required for this task = No and Does personnel perform this task more than 10 times a day? = Yes is Skill-Based Mode

Thank you for your help.

Re: Need DAX to format Yes or No question returning one answer.

Erokor — Wed, 11 Oct 2023 13:46:41 GMT

This is where I love the SWITCH() function. You can do something like the following in a measure (or custom column if you are binning/grouping for a slicer). The following assumes you Answer 1 & Answer 2 are from your list item 1, and Answer 4 & Answer 5 are from your Bullet point 3. Without knowing the column names the results are stored in, this is the closest answer I can get for you - but should put you on the right path!

for a bit more clarification SWITCH is being used to evaluate for a TRUE condition where in your list item 1. requires a value to be equal to "No" for answer 1 and "Yes" for answer 2, resulting in a combined "TRUE" output for your first list item. You'll want to understand what output you want for all combinations. Using an Excel sheet is a good way to ensure you have all possible combinations mapped out - and your desired output:

SWITCH( TRUE(), 'Table'[Answer 1] = "No" && 'Table'[Answer 2] = "Yes" , "Skill-Based Mode", 'Table'[Answer 3] = "Yes", "Skill-Based Mode", 'Table'[Answer 4] = "No" && 'Table'[Answer 5] = "Yes", "Skill-Based Mode" )

Re: Need DAX to format Yes or No question returning one answer.

dataanalyst2023 — Wed, 11 Oct 2023 14:54:23 GMT

Thank you for the fast response! It did work as a column. I couldnt make it work as a Measure. The error below wouldnt let me move forward.

Re: Need DAX to format Yes or No question returning one answer.

Erokor — Wed, 11 Oct 2023 15:15:09 GMT

If you are planning on Filtering in your visuals by the output - I would say doing it in a Column is the more than likely the right route to go. If you are calculating a "Count" and want an output, you can do this using an iterator to give Row Context.

Re: Need DAX to format Yes or No question returning one answer.

Erokor — Wed, 11 Oct 2023 15:22:27 GMT

In addition, if I answered your question in my other reply - please be sure to mark it as the solution so others can find it more easily to aid in their searches for answers!

Re: Need DAX to format Yes or No question returning one answer.

dataanalyst2023 — Wed, 11 Oct 2023 15:57:49 GMT

Thank you again for your help.

Re: Need DAX to format Yes or No question returning one answer.

Erokor — Wed, 11 Oct 2023 16:01:29 GMT

My pleasure! It's one of the highlights of my day to be able to help out folks.