Conditional Distinct Count

[Anonymous]

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!

[nandukrishnavs]

@Anonymous 

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

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[Greg_Deckler]

Perhaps something like the attached PBIX.

The Final Answer = 
    VAR __Answer6 = MAXX(FILTER('Table',[Question #]=6),[Answer])
    VAR __Nos = COUNTROWS(FILTER('Table',[Answer]="No"))
    VAR __Yes = COUNTROWS(FILTER('Table','Table'[Answer]="Yes"))
    VAR __Answers = SELECTCOLUMNS('Table',"Answer",[Answer])
RETURN
    SWITCH(TRUE(),
        "No" IN __Answers || __Answer6 = "No","NO",
        NOT("No" IN __Answers) || __Answer6 = "No","NO",
        "No" IN __Answers && (__Answer6 = "Yes" || __Answer6 = "n/a"),"MAYBE",
        NOT("No" IN __Answers) && (__Answer6 = "Yes" || __Answer6 = "n/a"),
        "I'm sorry Dave, I can't do that"
    )

[Anonymous]

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?

[nandukrishnavs]

@Anonymous 

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

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂