Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

COUNTROWS Function to exclude repeated value

hello Experts.

I want to count the number of "Completed" Projects where the function will check if there is any repeat in Column "ID" and if there is any repeat then it will check if both "Status" for that "ID" is "Completed". 

similarly, if the repeated "ID" has one "Pending" status it will be counted as "Pending"

I will need 2 counts, one for ''completed'' one for  ''pending''

 

ID

IP_ID

Status

87031

IP1234AO01-

Completed

87032

IP1234AO02-

Pending

87033

IP1234AO03-

Completed

87033- 

IP1234AO04- 

 Pending

87034-

IP1234AO05-

Pending

87035-

IP1234AO06-

Completed

87035-

IP1234AO07-

Pending

87035-

IP1234AO08-

Completed

87036-

IP1234AO09-

Pending

87037-

IP1234AO10-

Completed

87037-

IP1234AO11-

Pending

Need Help
Tips and Tricks
Tutorial Requests
  • Jos_Woolley's avatar
    Jos_Woolley
    4 years ago

    Hi,

    Measure for 'Completed':

     

    Completed :=
VAR MyTable =
    SUMMARIZE(
        'Table',
        [ID],
        "ID Count", COUNTROWS( 'Table' ),
        "Completed Count", CALCULATE( COUNTROWS( 'Table' ), 'Table'[Overall ACL Status] = "Completed" )
    )
VAR CompletedCheck =
    SUMX( MyTable, 0 + ( [ID Count] = [Completed Count] ) )
RETURN
    CompletedCheck

     

    Regards

  • Jos_Woolley's avatar
    Jos_Woolley
    4 years ago

    Try:

    Pending :=
VAR MyTable =
    CALCULATETABLE( VALUES( 'Table'[ID] ), 'Table'[Status] = "Pending" )
RETURN
    COUNTROWS( MyTable )

    Regards

14 Replies

  • Jos_Woolley's avatar
    Jos_Woolley
    Solution Sage
    4 years ago

    Hi,

    Measure for 'Completed':

     

    Completed :=
VAR MyTable =
    SUMMARIZE(
        'Table',
        [ID],
        "ID Count", COUNTROWS( 'Table' ),
        "Completed Count", CALCULATE( COUNTROWS( 'Table' ), 'Table'[Overall ACL Status] = "Completed" )
    )
VAR CompletedCheck =
    SUMX( MyTable, 0 + ( [ID Count] = [Completed Count] ) )
RETURN
    CompletedCheck

     

    Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello Jos_Woolley 
      Thank you for the help; this expression is not checking the duplicate numbers for the same status; as an example,

      87035 is three times in the ID column and only to IP_ID is Completed One Pending. so it (87035)should not return COMPLETE or be counted as complete as all 3 IP_IDs for 87035 is not complete.

      • Jos_Woolley's avatar
        Jos_Woolley
        Solution Sage
        4 years ago

        Not sure what you're doing, but the formula I posted does not consider 87035 as Completed. For the first dataset you posted it returns 1, since ID 87031 is the only ID which matches your criteria.

  • PijushRoy's avatar
    PijushRoy
    Community Champion
    4 years ago

    Hi Anonymous 

    Please try this for Status Completed
     = VAR _unique = CONCATENATE(ID, STATUS)
    VAR _completed = CALCULATE(DISTINCTCOUNT(_unique),'Table'[Status]="Completed')
    RETURN
    SWITCH(
    TRUE(),
    _completed > 0, _completed,
    CALCULATE(DISTINCTCOUNT(_unique),'Table'[Status]="Pending')

     

    If not solved, please share sample data and keep posted
    If solve your requirement, please mark this answer as SOLUTION
    If this comment helps you, please LIKE this comment/Kudos

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello PijushRoy 

      sorry mate encountered some problem, this is the error

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        PijushRoy  can you please again have a look?

    • amitchandak's avatar
      amitchandak
      Super User
      4 years ago

      Anonymous , In case you need two measures

      Completed= Countrows(summarize(filter(Table, Table[Status] = "Completed"),[ID]))

       

      pending = Countrows(summarize(filter(Table, Table[Status] = "Pending"),[ID]))

       

      In case you need pending which are not completed , I can give new measure

       

      If this does not help, share expected numbers for sample data