Forum Discussion

Still-Learning's avatar
Still-Learning
New Member
6 years ago

SIMPLE? - data modeling question

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.

 

ClientDocument TypeDate of Document
Joeassessment1/1/2020
Karenassessment1/5/2020
MarkAssessment1/9/2020
Karenaction plan2/9/2020
Joeaction plan2/14/2020
Billassessment1/16/2020
Billaction plan2/2/2020
Joeassessment1/3/2019
Karenassessment1/9/2019
MarkAssessment1/8/2019
Karenaction plan2/4/2019
Joeaction plan2/10/2019
Billassessment1/17/2019
Billaction plan2/9/2019
Need Help

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    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!

     

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    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,

     

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    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,