Forum Discussion

Raj12's avatar
Raj12
Icon for Helper III rankHelper III
2 years ago
Solved

Creating Measure to calculate unique clients by group

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"

IDPauses
W5021No Pause
W5021Pause
W5021Pause
W3111No Pause
W3111No Pause
W2011Pause

 

Result  
PausesId's CountReason
Pause2W2011 and W5021 (has one Pause , so should be exluded from No Pause)
No Pause1W3111 has No Pause only
Need Help
Show and Tell
Tips and Tricks
  • jdbuchanan71's avatar
    jdbuchanan71
    2 years ago

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

     

5 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      Raj12
      Icon for Helper III rankHelper III
      2 years ago

      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

    • Raj12's avatar
      Raj12
      Icon for Helper III rankHelper III
      2 years ago

      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's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    2 years ago

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