Trying to create a summary table of min, max, most recent across different categories per client ID

[Joacb761]

hey everyone,

I have a situation where I have an assessment that is given to clients at a structured intervals. It's item on the assessment is a different category where the hope is to decrease the score from 3 - 0. I am trying to create a bar visual that shows the average score on each item at BASELINE (the first time the assessment is given), EVER (the max score on that item that each client has, and LAST (score on the most recent assessment). I'm hitting a wall on how to create the summary table I believe I need. Can anyone help?

I currently have two tables

Assessment (Client per row)

Client	AssessmentID	Date	category1	category2
90210	373543	9/1/2022	3	1
90210	373544	12/1/2022	1	2
90211	373545	9/1/2022	0	0
90211	221210	12/1/2022	1	2
90211	221210	3/1/2023	2	2

Assessment (value per row)

AsssessmentID	category	value
373543	1	3
373543	2	1
373544	1	1
373544	2	2

I'm trying to create a third summarized table that looks like this:

ClientID	Category	Rating	TimeFrame
90210	category1	3	baseline
90210	category1	3	ever
90210	category1	1	last
90210	category2	2	baseline
90210	category2	3	ever
90210	category2	0	last
90211	category1	1	baseline
90211	category1	2	ever
90211	category1	0	last
90211	category2	1	baseline
90211	category2	2	ever
90211	category2	2	last

[v-yueyunzh-msft]

Hi , @Joacb761 

Thanks for your sample data , and for your description, you want to get  "BASELINE (the first time the assessment is given), EVER (the max score on that item that each client has, and LAST (score on the most recent assessment).".

But the end result in your table , i am not surely understand how we can calcualte this by your two tables:

Can you provide some more detailed calculation logic to us like a mathematical formula?

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly