Forum Discussion
Matrix Visual with Changing Names
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!
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.
5 Replies
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-d0cf2fcf92d0Hi,
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.
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.
