Forum Discussion
Helper IDAX measure for finding duplicates
Hi,
I have this table in Power BI and a slicer connected to column 'Trade date'. I need a measure that finds duplicates in column 'Security ID' in time interval chosen by slicer. Can anyone help?
| Trade date | Security ID |
| 2024-05-15 | CITI 240515 |
| 2024-05-15 | GS RPW 241127 |
| 2024-05-15 | GS RPW 241127 |
| 2024-05-15 | JPM AG 241113 |
| 2024-05-15 | JPM AG 241113 |
| 2024-05-15 | UBS S 241113 |
| 2024-05-15 | CITI 241127 |
| 2024-05-15 | CITI 241127 |
| 2024-05-15 | JPM RPJ 241127 |
| 2024-05-15 | JPM RPJ 241127 |
| 2024-05-15 | CITI 241211 |
| 2024-05-15 | CITI 241211 |
6 Replies
- DataNinja777
Super User
Hi Shota_Xuc ,
You can simply write a countrows dax formula like below, and bring 'Security ID' in the rows of your matrix table and put [Countrows] measure in the matrix to get the output you are after.
I attach the simple example pbix file.
Shota_XucDataNinja777 I simplified my table in my previous question, but my table has many columns besides 'Sequrity ID' column and I I want to find duplicates only in 'Security ID' column. Your solution does not work for tables with many columns.
Hi Shota_Xuc ,
Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
- Shota_Xuc
Hi Anonymous thanks, I found solution to find duplicates and it works good, below DAX:
Duplicate Sec ID =VAR CountSecID = COUNTROWS(FILTER(ALLSELECTED(DM_TRANSACTIONS_CURRENT_YEAR),DM_TRANSACTIONS_CURRENT_YEAR[Security ID] = MAX(DM_TRANSACTIONS_CURRENT_YEAR[Security ID])))RETURNIF(CountSecID > 2,"Duplicate","OK")
- Shota_Xuc
DataNinja777 countrows only works when you have one column in the table, but I had more columns and needed to count duplicates in a certain column.
