DAX measure for finding duplicates

[Shota_Xuc]

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

[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.

[Shota_Xuc]

@DataNinja777 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.

[Anonymous]

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])

    )

)

RETURN

    IF(CountSecID > 2,

    "Duplicate",

    "OK")

[Anonymous]

HI @Shota_Xuc,

I'm glad to hear you find the solution and share the expression here, I think they should help other users who faced the similar scenario.

Regards,

Xiaoxin Sheng

[DataNinja777]

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.