I have a dataset that has various duplicate rows, and I want to filter them on a criteria

[rohaanzuberi]

Hello,

I have a dataset that consists of various distinct and duplicate rows and I want to filter my dataset in Power BI to filter duplicate rows to show as distinct based on my conditions.

I want the following table to return results on following criteria:

• For distinct rows return as-is
• For duplicates, filter on the following condition:
  • If Order Number consists of two same products then return only one row. Example, If order consists of duplicate rows with "Apple" as the Product Category then return only one row of Order Number and Product Category
  • If Order Number consists of Apple and Orange then return Orange.
  • If Order Number consists of Orange and Mango then return Mango.

The priority level of filteration could be as Mango > Orange > Apple, meaning any row consisting of Mango should return Mango regardless of Orange or Apple as the other duplicate.

Example:

Order Number	Product Category
1	Apple
1	Apple
2	Apple
2	Orange
3	Orange
3	Mango
4	Apple
5	Apple

[Ashish_Mathur]

Hi,

Write this measure

Measure = if(CALCULATE(COUNTROWS(Data),Data[Product Category]="Mango"),"Mango",if(CALCULATE(COUNTROWS(Data),Data[Product Category]="Orange"),"Orange",MIN(Data[Product Category])))

Hope this helps.

[v-yueyunzh-msft · Accepted solution]

Hi, @rohaanzuberi 

Thanks for your sample data first~

According to your description, you want to filter duplicate rows to show as distinct based on my conditions.

Here are the steps you can rerfer to :
(1)This is my test data :

When we put the table in the visual , it can auto filter the duplicate rows when the [Order Number] and the [Product Category] all the same.

(2)We can create a measure  like this:

Measure = var _cur_order =MAX('Table'[Order Number])
var _t =SELECTCOLUMNS( CALCULATETABLE( ALLSELECTED('Table') , 'Table'[Order Number] =_cur_order), "Category" , [Product Category])
var _show =IF("Mango" in _t , "Mango" , IF( "Orange" in _t , "Orange" , IF( "Apple" in _t , "Apple")))
return
IF( MAX('Table'[Product Category]) = _show ,1,0)

Then we can put this fields on the visual and configure it on the "Filter on this visual":

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly