SIMPLE? - data modeling question

[Still-Learning]

Hello,

I am newer to PowerBI so I appreciate any help. 

My desired end state is a table with one row per client and one column per documentation type. In the respective documentation type columns, I'd like to only see the most recent document completed for that client.

As you can see below, there are multiple rows for each client as well as multiple rows for each matching documentation type and their dates, typically a year apart. For example, for Joe, there are two assessments and two action plans but I'd like to see one row for Joe and an Assessment column and an Action Plan column. In the columns, I'd like to have his 1/1/20 assessment and 2/14/20 action plan.

I tried, simply, pivoting on the doc type columnw ith the date column as the value but because there are multiples of each doc type for each client, it is returning multiple rows.

Ideally, I'd love this to be done within PBI rather than in the source file since I am doing a combine and load from folder refresh.

A sample of the data is below.

thank you for your help.

Client	Document Type	Date of Document
Joe	assessment	1/1/2020
Karen	assessment	1/5/2020
Mark	Assessment	1/9/2020
Karen	action plan	2/9/2020
Joe	action plan	2/14/2020
Bill	assessment	1/16/2020
Bill	action plan	2/2/2020
Joe	assessment	1/3/2019
Karen	assessment	1/9/2019
Mark	Assessment	1/8/2019
Karen	action plan	2/4/2019
Joe	action plan	2/10/2019
Bill	assessment	1/17/2019
Bill	action plan	2/9/2019

[v-lid-msft]

Hi @Still-Learning ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[v-lid-msft]

Hi @Still-Learning ,

If you want to visual it as a table, we can just put the date in filed and set it summarize as latest date:

If you want to get a actual table, we can create a calculated table as following:

Calculated Table = SUMMARIZE('Table','Table'[Client],'Table'[Document Type],"MaxDate",MAX('Table'[Date of Document]))

Or create the table in power query editor by using group by:

If it doesn't meet your requirement, Could you please show the exact expected result based on the tables that you have shared?

By the way, PBIX file as attached.

Best regards,

[parry2k]

@Still-Learning add the following measure

Max Date by Client and Dco Type = CALCULATE ( MAX ( 'Table'[Date of Document] ), ALLEXCEPT ( 'Table', 'Table'[Client], 'Table'[Document Type] ) )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!