How can I identify duplicates in a filtered column?

[Anonymous]

Hi
I'm looking for some help with identifying rows as duplicates ONLY if they are duplicated in the current filter context.

I'm having a really hard time doing this.
I can easily mark a duplicate row by creating a custom column by itterating through the table then adding a Yes/No or 1/0 but that only works in the context of the whole dataset when using a custom column.

E.g. whole dataset might look like

Date	ID
2020-01-01	A
2020-01-01	B
2020-01-02	C
2020-01-03	A

In my report visual I just pin a simple table containing both columns and a Date slicer against the date. 

If my slicer date range is the entire table then I need ID=A marked as duplicate.
If my slicer date range is only for the first 2 dates (2020-01-01 -> 2020-01-02) then there are no duplicates.

Any help would be great!

[Greg_Deckler · Accepted solution]

Try using ADDCOLUMNS to COUNTROWS against a SUMMARIZE

[Anonymous]

Thanks for pointing me in the right direction.

I ended up using that to create a new table with ID and number of duplicates. Initially it was just showing the whole table regardless of my date slicer then I reasised the slicer was using the date from the other table.... so I created a master calendar table and use that as the date slicer instead so it now affects both my DAX calculated table and the original.

Basically I created a table using this:

Table = 
        ADDCOLUMNS(
            SUMMARIZE(Sheet1,Sheet1[ID], Sheet1[Date]),
            "Duplicate",calculate(COUNTROWS(Sheet1))
        )

Then a master calendar table using CALENDARAUTO() and creating a relationship between the date in the master calendar and Sheet1 & Table.

I also created another bridging table for [ID] so I can now also drill through from my newly created table using [ID] to 'Sheet1'.