Forum Discussion
view duplicate values in dashboard
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
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}))
9 Replies
- PattemManohar
Community Champion
Avia Please provide any sample data and expected output to suggest an accurate solution.
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)
- PattemManohar
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}))
