COUNTROWS Function to exclude repeated value

[Anonymous]

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

[Jos_Woolley · Accepted solution]

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 · Accepted solution]

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]

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]

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]

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]

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]

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

[Jos_Woolley · Accepted solution]

Try:

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

Regards

[Anonymous]

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

[Anonymous]

@amitchandak  Can you please have a look

[amitchandak]

@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

[PijushRoy]

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]

Hello @PijushRoy 

sorry mate encountered some problem, this is the error

[Anonymous]

@PijushRoy  can you please again have a look?

[PijushRoy]

Please create sample data for 5, 6 rows with header (sample) and paste data here.
So I can share exact code, you will change the col name or data