Selectively remove duplicate rows based upon data in other cells

[j4jules]

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

[Anonymous]

Hi j4jules,
My solution in images.

[Distinct] measure image.

Tell us if works for you.

[j4jules]

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.

[alena2k]

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

[j4jules]

Thank you very much!  These really helped.

[Anonymous · Accepted solution]

Answer was made in Power Pivot
So it's exactly the same in PBI.