Display Only Duplicate Values in a slice

Hello everyone.
I have a table with a column called [Caixa]. This column has duplicate values.

I want to get a slice with only all the duplicate values ​​in that column.
I swear I read all the posts about duplicates and couldn’t find a solution and I can’t figure out where MAXROW () can help me.
All help is welcome.
Thanks

1 Like

Hi @Sergio_Sa,

If I have understood your requirement correctly, you may wish to use following expression in the row filter condition of the slice settings pane to get such a slice of all duplicate or repeating values of the column [Caixa]

IN([Caixa], SELECT(Your Table Name[Caixa], NOT(IN([Key Column], LIST([_THISROW].[Key Column])))))

3 Likes

Something like this should work:

COUNT(SELECT(Table Name[Key Column], AND([Column 1]=[_THISROW].[Column 1],[Column 2]=[_THISROW].[Column 2],[Column 3]=[_THISROW].[Column 3],etc.)))>1

This would might be a bit sync time intensive because its comparing 3 columns in every every row to every row. If there is a specific column that only duplicates would share you could remove the AND() expression and just leave one column comparison and that would vastly midigate its possible affect on sync time!

1 Like

Thank you very much.
That was exactly what I intended.

Thank you for your help.

3 Likes

Hi Suvrutt,
How can i reverse this formula by retaining only unique value in row filter slice but select the latest value in row?

IN([Caixa], SELECT(Your Table Name[Caixa], NOT(IN([Key Column], LIST([_THISROW].[Key Column])))))
much thanks
Mario

I also have the same question. I need my slice to only retain the unique values. Thank you!

Please share some more details of the data structure ( of course test data) so that the community could respond with an appropriate solution, if available.

1 Like

Hi found the formula to use from another thread:
[_THISROW] = INDEX( FILTER( Categories, [UpperCategory] = [_THISROW].[UpperCategory] ) , 1 )),
(NOT([Upper Category List] =0)

Basically the table I wanted to slice had columns with duplicate row entries, so the slicer was supposed to extract only the unique values of that row so the slice could be used to display an aggregate pie chart.
'Hope this helps!

2 Likes

Thank you. Good to know that you have got a solution. Yes, to search community for similar posts as one’s query is one of the good approaches to get a solution- if not exact, a similar one.

2 Likes

Hi @Suvrutt_Gurjar , can you walk me through how this achieves the result? I used it today in my App and it worked as well but I’m not understanding why it’s working to identify dups.

From my understanding, the SELECT() expression will scan the list of values in Yout Table Name[Caixa] capturing all rows for which the NOT() expression is TRUE. But what is the NOT() expressions doing?

NOT(IN([Key Column], LIST([_THISROW].[Key Column])))

I don’t understand how this NOT() is picking off dups. I’d love to understand this better - there’s something I’m just not understanding.

Thanks in advance.

To clarify, I believe it’s actually the IN() expression that’s picking off the dups and the NOT() expression is simply flipping their value; however, same issue i.e., how is the IN() expression here isolating rows with duplicate values in the [Caixa] column.

Hi @Orion_Szathmary

In the above expression, the condition part in SELECT() statement, NOT(IN([Key Column], LIST([_THISROW].[Key Column]))) helps creates a list of column [Caixa] values of all the rows EXCEPT the value of [Caixa] in the current row.

The part NOT(IN([Key Column], LIST([_THISROW].[Key Column]))) condition excludes the current row column value. So apart from the current row’s column value of [Caixa], if the same value exists in any of the other rows, even one other row for [Caixa], it is flagged as duplicate.

3 Likes