Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
rohaanzuberi
New Member

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

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 NumberProduct Category
1Apple
1Apple
2Apple
2Orange
3Orange
3Mango
4Apple
5Apple
1 ACCEPTED SOLUTION
v-yueyunzh-msft
Community Support
Community Support

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 :

vyueyunzhmsft_0-1681956184929.png

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.

vyueyunzhmsft_1-1681956240331.png

(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":

vyueyunzhmsft_2-1681956298576.png

 

 

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

 

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-yueyunzh-msft
Community Support
Community Support

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 :

vyueyunzhmsft_0-1681956184929.png

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.

vyueyunzhmsft_1-1681956240331.png

(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":

vyueyunzhmsft_2-1681956298576.png

 

 

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

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.