Matrix Visual with Changing Names

[shuhn1229]

Hi all,

I've been trying to think through a solution to this all afternoon and am drawing blanks. I have a table like the below:

Name	Date	ID
Book1	1/1/2023	1
Book1	2/1/2023	1
Result1	3/1/2023	1
Book2	2/1/2023	2
Result2	3/1/2023	2
Book3	3/1/2023	3

I am trying to build a matrix visual where I can visualize the latest value (max date) in name per row, showing a count if that ID appeared in a given month . The problem is when I create a matrix of name by row, if the name changes I get two or more rows per ID. If I create a matrix of ID by Date is all works perfectly, but ID is meaningless for me, I really need to be able to show the row name as the max value per date, with a record of each of the dates in aggregated by ID.

I've tried doing some summary tables but can't quite get this to look right. Am I overthinking it?

Thanks!

[amitchandak]

@shuhn1229 , Try Measure like

Last ID = Var _max = maxx(filter( ALLSELECTED(Data1), Data1[Name] = max(Data1[Name]) ),Data1[Date])
return
CALCULATE(Max(Data1[ID]), filter( (Data1), Data1[Name] = max(Data1[Name]) && Data1[Date] =_max))

Latest
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...

[shuhn1229]

Hi,

thank you so very much for your help. I was thinking this through further and think it might be easier in this case if I could create a calculated column that would 1) filter by ID 2) filter by latest possible date 3) return the Name column. Could you walk me through how to set this up? This way I'd be able to use the Name column for each row in the matrix.

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

Column = LOOKUPVALUE(Data[Name],Data[Date],CALCULATE(MAX(Data[Date]),FILTER(Data,Data[ID]=EARLIER(Data[ID]))),Data[ID],Data[ID])

Hope this helps.

[shuhn1229]

you are brilliant. thank you.

[Ashish_Mathur]

You are welcome. Thank you for the kind words.