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

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
Icon for Solution Sage rankSolution 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Jos  Yes you are right thats what I want. let me run it again will let you know the result this time

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello Jos_Woolley 

    What changes do i have to bring if I want to get number of""In Progress"" if any of the "ID" has In Progress in it?

    In this case, it will be 6 ( everything except 87031)
    Thanks

    • Jos_Woolley's avatar
      Jos_Woolley
      Icon for Solution Sage rankSolution Sage
      4 years ago

      Hi Anonymous ,

      Bit confused about this last request. ID 87031 does not appear in the data you gave in Message #6. It does appear in the data you gave in Message #1, but then none of the entries for that data have a status of "In Progress".

      Regards

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hello Jos_Woolley 
        Sorry for my Friday's brain; please see below; I hope it makes sense now.

        What changes do I have to bring in the DAX formula if I want to get the number of "Pending" status if any of the "ID" has "Pending" in it?

        In this case(*message reference  #1), the total "ID" number of "Pending" status will be 6 ( everything except 87031)


        Thanks