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 III

## 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
🙂

4 REPLIES 4
Highlighted
Super User IV

## 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"
)``````

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

##### 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 III

## 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
🙂

Highlighted
Super User III

## 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
🙂

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors