Count and list duplicate records.

[Leebc]

Hi,

I am new to DAX and Power BI and need some assistance.

I have a table of clients who submit documents for approval. A new record using the client_ID is created for each time a client submits a document. I want to be able to list clients who have made 1 or more submissions and display client_ID, dates of each submission as well as the title of the document.

Regards
Colin

[dkay84_PowerBI]

Based on your description, you may not need any DAX.  I'm not sure without a pic or sample data, but a matrix visual might do what you want:

Put Client ID, Submission Date and Document Title on the rows (in that order) and it should aggregate the data, so that for each Client ID, you will see every Submission Date and for each Submission Date, you will see the Document Title.

[tringuyenminh92]

Hi @Leebc,

Could you share current data structure or sample data? So I could quickly try and propose solution.

[Leebc]

Simplistically, the data goes something like:

Client_ID	Submission_Date	Title
1	1/10/2016	AAA
2	2/10/2016	BBB
5	3/10/2016	AAA
2	4/10/2016	CCC
3	10/11/2016	AAA
4	12/11/2016	AAA
5	22/12/2016	BBB
1	2/01/2017	CCC
5	5/01/2017	CCC
5	8/01/2017	DDD
5	10/01/2017	EEE

By executing a query on the data  I want to identify the number of submissions per client together with titles and dates of submission.  

The type of output I am looking for is:

1 submission: Client 3 submitted 1 document on 10/11/2016 and titled AAA

2 submission: Client 1 submitted 2 documents on 1/10/2016 and 2/01/2017 and titled AAA and CCC respectively

5 submissions: Client 5 submitted 5 documents on 3/10/2016, 22/12/2016, 5/01/2017 etc. and titled AAA, BBB

and CCC respectively

I trust this makes sense?

[v-huizhn-msft · Accepted solution]

Hi @Leebc,

As the @tringuyenminh92 posted, you should create a Clustered column chart displays how many documents submitted for each client. Create a treemap including title as group level, Submission_Date as detial level, the count of titles as value level.



Then you should add a slicer as follows, when you click the Cient1, the column chart displays Cient1 submitted 2 documnets, the treemap shows the documents' titles and date. When you click Cient5, it returns all the information about Cient5, please see the following second screenshot.




If you have any problem, please feel free to ask.

Best Regards,
Angelia

[tringuyenminh92]

Hi @Leebc,

I got your point but i'm not sure your expectation could be displayed in one graph/chart. So you could achieve it by use column chart and treemap as below picture: