cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Conditional Distinct Count

I need to calculate the follow conditions against a dataset in which I will ultimately receive one distinct final answer for each ID (each ID contains 6 questions each):

 Questions 1-5 Question 6 Final Answer ANY = "No" "No" NO ALL = "Yes" OR "n/a" "No" NO ANY = "No" "Yes" OR "n/a" MAYBE ALL = "Yes" OR "n/a" "Yes" OR "n/a" YES

SAMPLE DATASET

 ID Question # Answer 1 1 Yes 1 2 Yes 1 3 No 1 4 Yes 1 5 No 1 6 Yes 2 1 Yes 2 2 Yes 2 3 Yes 2 4 Yes 2 5 Yes 2 6 No 3 1 n/a 3 2 No 3 3 Yes 3 4 No 3 5 Yes 3 6 Yes

The result after the conditions have been applied would be grouped something like:

 ID Final Answer 1 Yes 2 Maybe 3 No

(the final answer above is not accurate with the conditions for each ID, but just sharing to give an idea of what the final result should look like)

Any help or pointers in the right direction would be greatly appreciated!!

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV

## Re: Conditional Distinct Count

Here is the simplified version

``````FinalAnswer =
var _onetofive= COMBINEVALUES(",",'Table'[Question 1],'Table'[Question 2],'Table'[Question 3],'Table'[Question 4],'Table'[Question 5])
var _six='Table'[Question 6]

var _condition1= IF(CONTAINSSTRING(_onetofive,"No") && (_six="No"),"NO")

var _condition2= IF(NOT(CONTAINSSTRING(_onetofive,"No")) && (_six="No"),"NO")

var _condition3= IF(CONTAINSSTRING(_onetofive,"No") && (_six IN {"Yes","n/a"}),"MAYBE")

var _condition4=IF(NOT(CONTAINSSTRING(_onetofive,"No")) &&(_six IN {"Yes","n/a"}),"YES")

VAR _result =
SWITCH (
TRUE (),
NOT (
ISBLANK ( _condition1 )
), _condition1,
NOT (
ISBLANK ( _condition2 )
), _condition2,
NOT (
ISBLANK ( _condition3 )
), _condition3,
NOT (
ISBLANK ( _condition4 )
), _condition4,
"Not satisfied"
)
RETURN
_result``````

Appreciate with a kudos
🙂

Regards,
Nandu Krishna

Appreciate with a kudos 👍

Proud to be a Super User!

4 REPLIES 4
Highlighted
Super User IX

## Re: Conditional Distinct Count

Perhaps something like the attached PBIX.

``````The Final Answer =
RETURN
SWITCH(TRUE(),
"I'm sorry Dave, I can't do that"
)``````

---------------------------------------

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Frequent Visitor

## Re: Conditional Distinct Count

Thanks Dave!

I pivoted the table and now am trying to apply what you shared to this:

 ID Question 1 Question 2 Question 3 Question 4 Question 5 Question 6 Final Answer 1 Yes Yes Yes Yes Yes n/a 2 Yes No n/a n/a n/a No 3 Yes No No No No No 4 n/a No Yes Yes No Yes 5 No No No No No No 6 Yes Yes Yes No Yes Yes 7 n/a n/a No Yes No No 8 Yes Yes Yes Yes Yes Yes 9 No No No No No Yes 10 n/a Yes n/a Yes Yes Yes

However, I kept getting a table of multiple values was supplied error...

Can you help?

Highlighted
Super User IV

## Re: Conditional Distinct Count

``````Final Answer =
VAR _condition1 =
IF (
( 'Table'[Question 1] = "No"
|| 'Table'[Question 2] = "No"
|| 'Table'[Question 3] = "No"
|| 'Table'[Question 4] = "No"
|| 'Table'[Question 5] = "No" )
&& ( 'Table'[Question 6] = "No" ),
"NO"
)
VAR _condition2 =
IF (
( 'Table'[Question 1]
IN {
"Yes",
"n/a"
}
&& 'Table'[Question 2]
IN {
"Yes",
"n/a"
}
&& 'Table'[Question 3]
IN {
"Yes",
"n/a"
}
&& 'Table'[Question 4]
IN {
"Yes",
"n/a"
}
&& 'Table'[Question 5]
IN {
"Yes",
"n/a"
} )
&& ( 'Table'[Question 6] = "No" ),
"NO"
)
VAR _condition3 =
IF (
( 'Table'[Question 1] = "No"
|| 'Table'[Question 2] = "No"
|| 'Table'[Question 3] = "No"
|| 'Table'[Question 4] = "No"
|| 'Table'[Question 5] = "No" )
&& ( 'Table'[Question 6]
IN {
"Yes",
"n/a"
} ),
"MAYBE"
)
VAR _condition4 =
IF (
( 'Table'[Question 1]
IN {
"Yes",
"n/a"
} )
&& ( 'Table'[Question 2]
IN {
"Yes",
"n/a"
} )
&& ( 'Table'[Question 3]
IN {
"Yes",
"n/a"
} )
&& ( 'Table'[Question 4]
IN {
"Yes",
"n/a"
} )
&& ( 'Table'[Question 5]
IN {
"Yes",
"n/a"
} )
&& ( 'Table'[Question 6]
IN {
"Yes",
"n/a"
} ),
"YES"
)
VAR _result =
SWITCH (
TRUE (),
NOT (
ISBLANK ( _condition1 )
), _condition1,
NOT (
ISBLANK ( _condition2 )
), _condition2,
NOT (
ISBLANK ( _condition3 )
), _condition3,
NOT (
ISBLANK ( _condition4 )
), _condition4,
"Not satisfied"
)
RETURN
_result``````

Appreciate with a kudos
🙂

Regards,
Nandu Krishna

Appreciate with a kudos 👍

Proud to be a Super User!

Highlighted
Super User IV

## Re: Conditional Distinct Count

Here is the simplified version

``````FinalAnswer =
var _onetofive= COMBINEVALUES(",",'Table'[Question 1],'Table'[Question 2],'Table'[Question 3],'Table'[Question 4],'Table'[Question 5])
var _six='Table'[Question 6]

var _condition1= IF(CONTAINSSTRING(_onetofive,"No") && (_six="No"),"NO")

var _condition2= IF(NOT(CONTAINSSTRING(_onetofive,"No")) && (_six="No"),"NO")

var _condition3= IF(CONTAINSSTRING(_onetofive,"No") && (_six IN {"Yes","n/a"}),"MAYBE")

var _condition4=IF(NOT(CONTAINSSTRING(_onetofive,"No")) &&(_six IN {"Yes","n/a"}),"YES")

VAR _result =
SWITCH (
TRUE (),
NOT (
ISBLANK ( _condition1 )
), _condition1,
NOT (
ISBLANK ( _condition2 )
), _condition2,
NOT (
ISBLANK ( _condition3 )
), _condition3,
NOT (
ISBLANK ( _condition4 )
), _condition4,
"Not satisfied"
)
RETURN
_result``````

Appreciate with a kudos
🙂

Regards,
Nandu Krishna

Appreciate with a kudos 👍

Proud to be a Super User!

Announcements

#### August Community Highlights

Check out a full recap of the month!

#### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors