Creating Measure to calculate unique clients by group

[Raj12]

Need to get Uniqie IDs count by group of Pause/No Pause. If ID has any 1 "Pause", then should be counted in only Pause category even though it has entry for "No Pause"

ID	Pauses
W5021	No Pause
W5021	Pause
W5021	Pause
W3111	No Pause
W3111	No Pause
W2011	Pause

Result
Pauses	Id's Count	Reason
Pause	2	W2011 and W5021 (has one Pause , so should be exluded from No Pause)
No Pause	1	W3111 has No Pause only

[jdbuchanan71 · Accepted solution]

Give this a try.

ID's Corrected = 
VAR _Pause = CALCULATETABLE ( DISTINCT ( YourTable[ID] ), YourTable[Pauses] = "Pause" )
VAR _NoPause = EXCEPT ( CALCULATETABLE ( DISTINCT ( YourTable[ID] ), YourTable[Pauses] = "No Pause" ), _Pause )
VAR _PauseNoPause = CALCULATETABLE ( DISTINCT ( YourTable[ID] ), YourTable[Pauses] IN {"Pause", "No Pause"} )
VAR _Missing = EXCEPT ( CALCULATETABLE ( DISTINCT ( YourTable[ID] ), YourTable[Pauses] = "Missing" ), _PauseNoPause )
VAR _Type = SELECTEDVALUE ( YourTable[Pauses] )
RETURN
    SWITCH (
        TRUE (),
        _Type = "Pause", COUNTROWS ( _Pause ),
        _Type = "No Pause", COUNTROWS ( _NoPause ),
        _Type = "Missing", COUNTROWS ( _Missing )
    )

[jdbuchanan71]

What is the logic for counting Missing vs No Pause?

[Raj12]

Its based on priority order 
1. Pause
2. No Pause
3. Missing
Example
if Id has even single Pause then output will have Pause
if Id has No Pause & Missing then output will have No Pause
if Id has only Missing then output will have Missing
if Id has Pause & Missing then output will have Pause
if Id has Pause , No Pause & Missing then output will have Pause

[jdbuchanan71]

@Raj12 

I believe this will do what you are looking for.  My test gives me the result you want at least.

ID's Corrected = 
VAR _Pause = CALCULATETABLE ( DISTINCT ( YourTable[ID] ), YourTable[Pauses] = "Pause" )
VAR _NoPause = EXCEPT ( DISTINCT ( YourTable[ID] ), _Pause )
VAR _Type = SELECTEDVALUE ( YourTable[Pauses] )
RETURN
    SWITCH (
        TRUE (),
        _Type = "Pause", DISTINCTCOUNT ( YourTable[ID] ),
        _Type = "No Pause", COUNTROWS ( _NoPause )
    )

[Raj12]

Hi, Thank you for the response, solution in great. However if there is addition value in Pauses column 
column "Missing" then how to count that in measure?
     ID     Pauses
W5021 No Pause
W5021 Pause
W5021 Pause
W3111 No Pause
W3111 Missing
W2011 Pause
W1111 Missing

Output as :
Pauses      Id
Pause        2
No Pause  1
Missing     1