Forum Discussion
Identify duplicates across 2-x column combinations
Hello PowerBI Users
currently I'm searching for a solution to mark double entries in a timetable->employee list.
My table looks like this:
| StartTimeDate | EndTimeDate | Employee |
| 01.01.2024 08:00:00 | 01.01.2024 13:00:00 | a |
| 01.01.2024 08:00:00 | 01.01.2024 14:00:00 | b |
| 01.01.2024 08:00:00 | 01.01.2024 15:00:00 | a |
| 01.01.2024 09:00:00 | 01.01.2024 16:00:00 | c |
Now I need a DAX formular to find duplicates in the combination StartTimeDate+Employee (in the example table should be row 2 and 4), and another formular to find a duplicate in the combination StartTimeDate+EndTimeDate+Employee
Maybe someone has a solution?
Thanks and best regards, Dominik
pls try this
Hi Dominik82 ,
Ahmedx ,good answer!
And you can also use EARILER in the DAX:
Use these DAX to create new columns:Start + Employee = IF( COUNTROWS( FILTER( 'Sheet23', 'Sheet23'[StartTimeDate] = EARLIER(Sheet23[StartTimeDate]) && 'Sheet23'[Employee] = EARLIER(Sheet23[Employee]) ) ) > 1, 1, BLANK() )Start + End + Employee = IF( COUNTROWS( FILTER( 'Sheet23', 'Sheet23'[StartTimeDate] = EARLIER(Sheet23[StartTimeDate]) && 'Sheet23'[EndTimeDate] = EARLIER(Sheet23[EndTimeDate]) && 'Sheet23'[Employee] = EARLIER(Sheet23[Employee]) ) ) > 1, 1, BLANK() )The final output is as below:
Best Regards,
Dino Tao
If these posts help, then please consider both of the answers to Accept it as the solution to help the other members find it more quickly.
3 Replies
- Ahmedx
Super User
pls try this
Hi Dominik82 ,
Ahmedx ,good answer!
And you can also use EARILER in the DAX:
Use these DAX to create new columns:Start + Employee = IF( COUNTROWS( FILTER( 'Sheet23', 'Sheet23'[StartTimeDate] = EARLIER(Sheet23[StartTimeDate]) && 'Sheet23'[Employee] = EARLIER(Sheet23[Employee]) ) ) > 1, 1, BLANK() )Start + End + Employee = IF( COUNTROWS( FILTER( 'Sheet23', 'Sheet23'[StartTimeDate] = EARLIER(Sheet23[StartTimeDate]) && 'Sheet23'[EndTimeDate] = EARLIER(Sheet23[EndTimeDate]) && 'Sheet23'[Employee] = EARLIER(Sheet23[Employee]) ) ) > 1, 1, BLANK() )The final output is as below:
Best Regards,
Dino Tao
If these posts help, then please consider both of the answers to Accept it as the solution to help the other members find it more quickly.Thanks for your help.
That helped me a lot 😊
