Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hi, try with this calculated column
Unique = IF ( CALCULATE ( MAX ( Table1[Date] ); FILTER ( Table1; Table1[ID] = EARLIER ( Table1[ID] ) ) ) = Table1[Date]; "Yes"; "No" )
Regards
Victor
Lima - Peru
Hi, try with this calculated column
Unique = IF ( CALCULATE ( MAX ( Table1[Date] ); FILTER ( Table1; Table1[ID] = EARLIER ( Table1[ID] ) ) ) = Table1[Date]; "Yes"; "No" )
Regards
Victor
Lima - Peru
Hi Victor,
your solution worked perfect. I had to change the initial idea from the latest to the earliest date, but that was easy with your solution!
Thanks a lot,
Michael
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |