Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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!

4 REPLIES 4
nandukrishnavs
Super User
Super User

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


Regards,
Nandu Krishna

Greg_Deckler
Super User
Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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?

@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

Capture.JPG



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


Regards,
Nandu Krishna

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.