Display Only Duplicate Values in a slice

[Sergio_Sa]

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

[Suvrutt_Gurjar · Accepted solution]

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

[reggieneo]

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

[Orion_Szathmary]

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.

[Orion_Szathmary]

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.

[Suvrutt_Gurjar]

Hi @Orion_Szathmary

Suvrutt_Gurjar:

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

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.

[QREW_Cam]

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!

[Sergio_Sa]

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

Thank you for your help.

[Enrico_Emil_Del]

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

[Suvrutt_Gurjar]

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.

[Enrico_Emil_Del]

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!

[Suvrutt_Gurjar]

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.

[Sara_Bertomeu]

Please @Enrico_Emil_Del can you explain the details? thanks Sara