Forum Discussion
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...
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 CompletedCheckRegards
Try:
Pending := VAR MyTable = CALCULATETABLE( VALUES( 'Table'[ID] ), 'Table'[Status] = "Pending" ) RETURN COUNTROWS( MyTable )Regards
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
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.
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.
Hi Jos Yes you are right thats what I want. let me run it again will let you know the result this time