Forum Discussion
Selectively remove duplicate rows based upon data in other cells
Hi. I need some help regarding duplicate rows in a report. I have the following data and need to create a pivot table as shown below plus a bar chart. I need counts of all of the Req Names but not where there is another row with that same Req Name for the Policy # and show them by Office #. The Req Order Placed, Req Order Recieived and Description might be different, however I only need to count the number of distinct Req Names a policy had. I have created the Matrix but I am not sure of the best way to remove the duplicates in this situation. Any suggestions are appreciated. I can change the report or the SQL. Thank you in advance.
| Office # | Policy # | Agent | Req Order Placed | Req Order Received | Description | Req Name |
| 1 | 12345 | John Smith | 10/22/2018 | 10/22/2018 | Description ABC | Paystub |
| 1 | 12345 | John Smith | 10/23/2018 | Description DEF - A | Omission | |
| 1 | 12345 | John Smith | 10/23/2018 | Description DEF - B | Omission | |
| 1 | 12345 | John Smith | 10/23/2018 | Description DEF - C | Omission | |
| 1 | 12345 | John Smith | 10/23/2018 | Description DEF - D | Omission | |
| 7 | 45678 | Jane Johnson | 9/18/2018 | Description GHI | PDA | |
| 7 | 45679 | Jane Johnson | 9/18/2018 | 10/1/2018 | Description GHI | PDA |
| 18 | 21314 | Sam Jones | 10/25/2018 | Description MNO | Blood | |
| 12 | 10111 | Al Jackson | 10/1/2018 | Description DEF - B | Omission | |
| 12 | 10111 | Al Jackson | 10/1/2018 | 10/18/2018 | Description DEF - B | Omission |
| 1 | 16151 | Cindy Louis | 9/10/2018 | 9/18/2018 | Description JKL - for Signed and Dated | Illustration |
| 1 | 16151 | Cindy Louis | 9/18/2018 | 9/18/2018 | Description JKL - for Signature | Illustration |
| 1 | 16151 | Cindy Louis | 9/23/2018 | Description GHI | PDA | |
| 1 | 16151 | Cindy Louis | 9/23/2018 | 9/23/2018 | Description GHI | PDA |
| Need: | ||||||
| Office # | Paystub | Omission | Blood | PDA | Illustration | Grand Total |
| 1 | 1 | 1 | 1 | 1 | 4 | |
| 7 | 1 | 1 | ||||
| 12 | 1 | 1 | ||||
| 18 | 1 | 1 | ||||
| Grand Total | 1 | 2 | 1 | 2 | 1 | 7 |
5 Replies
Thank you so much for your reply. I need to do this in Power BI or the SQL that I use to pull the data in. I will see if I can do your method in Power BI.
My first idea was simply create a measure Req Count = DISTINCTCOUNT(Table1[Policy #])
It would eliminate dups but it will not give you a totals you are looking for.So, try to add a calculated column Key = COMBINEVALUES(" ", Table1[Req Name], Table1[Policy #]) and use it DISTINCTCOUNT:
Key Count = DISTINCTCOUNT(Table1[Key])
It shoudl do the trick
