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
Solved! Go to 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])))))
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])))))
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
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.
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.
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!
Thank you very much.
That was exactly what I intended.
Thank you for your help.
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.
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!
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.
Please @Enrico_Emil_Del can you explain the details? thanks Sara
User | Count |
---|---|
39 | |
35 | |
29 | |
23 | |
18 |