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" f...
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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

amitchandak  Can you please have a look

 

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper 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