topic Re: How can I identify duplicates in a filtered column? in DAX Commands and Tips

How can I identify duplicates in a filtered column?

Anonymous — Thu, 09 Apr 2020 03:24:28 GMT

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!

Re: How can I identify duplicates in a filtered column?

Greg_Deckler — Thu, 09 Apr 2020 04:21:51 GMT

Try using ADDCOLUMNS to COUNTROWS against a SUMMARIZE

Re: How can I identify duplicates in a filtered column?

Anonymous — Sat, 11 Apr 2020 07:13:55 GMT

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