Power BI - How to display only the values which appear more than once in a column

user_10619851 0
Viewed 1500

I have a table with multiple columns. One of these is 'EAN'. In this column there are supposed to be unique values. Unfortunatly this is not the case. Now I want to find all the values that appear more then once.

I tried the FILTER, EARLIER, COUNTROWS. Nothing gives the output I'm looking for.

Example:
Art A - 111
Art B - 123
Art C - 222
Art D - 222
Art E - 456
What I expect as output is just a table, column or chart where '222' appears.

dax powerbi
2 Answers

Here's one way just using Power Query and M Code:

let

//read in and type the data
    Source = Excel.CurrentWorkbook(){[Name="Table8"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Art", type text}, {"Num", Int64.Type}}),

//selectd only those rows where there are at least two identical nums
    filter = Table.SelectRows(#"Changed Type", each List.Count(List.Select(#"Changed Type"[Num], (li)=>li=[Num]))>1),

//sort the output to keep the duplicates together
    #"Sorted Rows" = Table.Sort(filter,{{"Num", Order.Ascending}})
in
   #"Sorted Rows"

enter image description here

user_28729220

Create your visual using the EAN field.

Then create a measure with the formula:

= COUNTROWS('Table')

and drag this measure into the filters pane, setting the condition to 'greater than 1'.

user_170077040
Related