view duplicate values in dashboard

[Avia]

Hi,

I am new to power bi and just learning how to use it.

I have a report connected by ODATA and I would like to create a dashboard where i can see only duplicate listings according to a specific column.

For example I have the following columns NAME ADDRESS PHONE and i want to view only those listings that have the name show up more then once (in sevral rows).

An additional level would be if I have a few columns with phone number and i want to find duplicates across the file. If one row shows a phone number that appears in another (not necessarily in the same column).

The thing is that i want it to stay dinamic so if I reffresh the data I will still see ALL the duplicats (including new ones)

Hope this is clear.

TIA

[PattemManohar]

@Avia Please provide any sample data and expected output to suggest an accurate solution.

[Avia]

NAME	PHONE1	PHONE2
A	1111
B	222	6666
C	3333
A	555
D	444
E	6666
D	777

1. I would like  to view duplicates by name only (A , D) -  the rest show up once so I am not interested in them.

2. Is there a way to view rows that have repeating valus such as phone for  B and E (and show them as correlating - in the data base there might be several diffrent listings with similar info)

[Avia]

Right new table THANKS! working now

[Avia]

fogot to tag thanks! pattemmanohar

[v-lili6-msft]

hi, @Avia

pattemmanohar had provided a way by creating a calculate table.

you can also create calculate column by these formula

1.search duplicates by name and then filter blank

has dup names = IF( CALCULATE(COUNTA(Basic[NAME]),FILTER(Basic,Basic[NAME]=EARLIER(Basic[NAME])))>1,Basic[NAME])

2.use LOOKUPVALUE Function to lookup to phone column like below

repeating phone = LOOKUPVALUE(Basic[PHONE1],Basic[PHONE1],Basic[PHONE2] )

Best Regards,

Lin

[PattemManohar · Accepted solution]

@Avia Thanks for that. Please try the below using "New Table" option for each DAX.

Test25Out1 = FILTER(SUMMARIZECOLUMNS(Test25[NAME],"CNT",COUNTROWS(Test25)),[CNT]>1)

Test25Out2 = 
VAR _Temp = FILTER(DISTINCT(Test25[PHONE2]),Test25[PHONE2]<>BLANK())
RETURN CALCULATETABLE(Test25,FILTER(Test25,Test25[PHONE1] IN {_Temp} || Test25[PHONE2] IN {_Temp}))

[Avia]

@PattemManohar Thank you for your help. Regarding the second option (Test25Out2 = ) what if I have 8 different columns with phone numbers (not just 2) ? how do I cross check them? do i need to create a separate formula for each or can I include all the columns in one?

[Avia]

What am i doing wrong?

[PattemManohar]

@Avia Please confirm that you ar trying this using "New Table" option ?

Note -  Please tag the person name to whom you are responding to otherwise they will not get notified about your response and you might get lost track of communication.