Reply
Regular Visitor
Posts: 31
Registered: ‎09-14-2016
Accepted Solution

Displaying rows with a duplicate same-column value

Hey all,

 

Relatively new to PBID, and unfamiliar with backend DAX queries. I'm probably overlooking something, as it seems this should be simple.

 

I have a table structured as below:

 

Row     Owner          Oppty     UniqueID    etc.

1          Person A       MSFT      1

2          Person B       AAPL       2

3          Person C      GOOG      3

4          Person D      AMZN      1

etc.

 

All I want to do is display all rows with duplicate UniqueIDs, e.g. rows 1 and 4, and filter out everything else. As far as I can tell, there's no built-in "Show items when the value: is duplicate" filter (though it'd be nice if there was). All the solved questions I've found have dealt with either filtering out the duplicates, or highlighting duplicates between two different columns.

 

Should be simple. Thanks for the help.


Accepted Solutions
Super Contributor
Posts: 878
Registered: ‎06-09-2016

Re: Displaying rows with a duplicate same-column value

Hi There,

 

You can use powerquery for such tasks. To identify all the rows with duplicate unique IDs,

 

You should perform these steps in order,

 

1. In PowerQuery Mode, Perform Group by operatio. This is under the Transform Tab. 

In Group By, Follow the steps shown in screen shot.Group ByGroup ByFilter out all the records in duplicate records column with Value 1Filter out all the records in duplicate records column with Value 1Expand the Oppty column by clicking on the two sided arrowsExpand the Oppty column by clicking on the two sided arrowsRemove duplicate records and unique IDs column and rename the headersRemove duplicate records and unique IDs column and rename the headers

 

Thanks & Regards,

Bhavesh

View solution in original post


All Replies
Super Contributor
Posts: 878
Registered: ‎06-09-2016

Re: Displaying rows with a duplicate same-column value

Hi There,

 

You can use powerquery for such tasks. To identify all the rows with duplicate unique IDs,

 

You should perform these steps in order,

 

1. In PowerQuery Mode, Perform Group by operatio. This is under the Transform Tab. 

In Group By, Follow the steps shown in screen shot.Group ByGroup ByFilter out all the records in duplicate records column with Value 1Filter out all the records in duplicate records column with Value 1Expand the Oppty column by clicking on the two sided arrowsExpand the Oppty column by clicking on the two sided arrowsRemove duplicate records and unique IDs column and rename the headersRemove duplicate records and unique IDs column and rename the headers

 

Thanks & Regards,

Bhavesh

New Contributor
Posts: 457
Registered: ‎08-03-2016

Re: Displaying rows with a duplicate same-column value

BV displays doing it in 4 steps of Query Editor.  Another approach is 4 steps of New filtered tables - in the example below each table is named: UniqueID1 thru UniqueID4

 

UniqueID1 is your starting table

 

UniqueID2 = GROUPBY(UniqueID1,
                                          UniqueID1[ID],
                                                 "DupeCount",COUNTX(CURRENTGROUP(),UniqueID1[ID]))

 

UniqueID3 = FILTER(UniqueID2,UniqueID2[DupeCount]>1)

 

at this point you then must create a join line between UniqueID1.ID and UniqueID3.ID fields

 

UniqueID4 = FILTER(UniqueID1,UniqueID1[ID]=RELATED(UniqueID3[UniqueID1_ID]))

 

UniqueID4 is your final table with the results you seek

 

Should be simple!! but is not........