Forum Discussion
Count duplicates only once in a column
Hi all,
I want to identify in a column (not as a measure) all values for a specific ID only once.
| ID | Date | Unique |
| 10 | 09.11.2017 | YES |
| 2 | 01.12.2017 | YES |
| 10 | 11.11.2011 | NO |
The respective formular in Excel works fine as follows:
=IF(MATCH(A4,A:A,0)=ROW(),"YES","NO")
What I wanted to do is, that the last entry of an ID is identified as unique (column C) and all past values of the same ID should be marked as "no". Therefore the DISTINCTCOUNT formular with filter is not working. In that case, all double values would be counted > 1. For ID 10 the outcoume would be 2 for both rows but I want to count ID excatly one time as unique.
I dont want to delete duplicate rows.
Does anyone has an idea how to solve that problem?
Regards
Michael
Hi Blizzard
Please try this column
Column = VAR IDCount = CALCULATE ( COUNTROWS ( Table1 ), ALLEXCEPT ( Table1, Table1[ID] ) ) = 1 VAR Last_Date = Table1[Date] = CALCULATE ( MAX ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[ID] ) ) RETURN IF ( OR ( IDCount, Last_Date ), "Yes", "No" )
2 Replies
- Zubair_Muhammad
Community Champion
Hi Blizzard
Please try this column
Column = VAR IDCount = CALCULATE ( COUNTROWS ( Table1 ), ALLEXCEPT ( Table1, Table1[ID] ) ) = 1 VAR Last_Date = Table1[Date] = CALCULATE ( MAX ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[ID] ) ) RETURN IF ( OR ( IDCount, Last_Date ), "Yes", "No" )Hi Zubair_Muhammad,
your code is working as well. For the first step I used the code presented here.
Unfortunately I double posted my question :(. But as your formular is working as well, I`m confident that I can use it for the next step in another calcualtion.
Thanks a lot,
Michael
