cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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-5Question 6Final 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

IDQuestion #Answer
11Yes
12Yes
13No
14Yes
15No
16Yes
21Yes
22Yes
23Yes
24Yes
25Yes
26No
31n/a
32No
33Yes
34No
35Yes
36Yes
 

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

IDFinal Answer
1Yes
2Maybe
3No

(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
Super User IV

Re: Conditional Distinct Count

@kbig02 

 

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
🙂




Regards,
Nandu Krishna

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

Proud to be a Super User!

View solution in original post

4 REPLIES 4
Highlighted
Super User IX
Super User IX

Re: Conditional Distinct Count

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

 


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

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:

 

IDQuestion 1Question 2Question 3Question 4Question 5Question 6Final Answer
1YesYesYesYesYesn/a 
2YesNon/an/an/aNo 
3YesNoNoNoNoNo 
4n/aNoYesYesNoYes 
5NoNoNoNoNoNo 
6YesYesYesNoYesYes 
7n/an/aNoYesNoNo 
8YesYesYesYesYesYes 
9NoNoNoNoNoYes 
10n/aYesn/aYesYesYes 

 

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

 

Can you help?

Highlighted
Super User IV
Super User IV

Re: Conditional Distinct Count

@kbig02 

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

Capture.JPG



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




Regards,
Nandu Krishna

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

Proud to be a Super User!

Highlighted
Super User IV
Super User IV

Re: Conditional Distinct Count

@kbig02 

 

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
🙂




Regards,
Nandu Krishna

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

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

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.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors