Matrix - text fields summarization

[austinhammer]

Hello,

I am trying to use a Matrix to display this data so that I can nest the columns.  I want to show the most recent Sample Date and the coresponding Action and Lab report number.  The Matrix summarizes these fields seperately.  How can I ensure the Action and Lab report number are coming from the same row as the Latest Sample Date?

[Greg_Deckler · Accepted solution]

@austinhammer So this is a double lookup. Watch the end of this video for the technique.

[austinhammer]

Thanks @Greg_Deckler  - great video! 

I'm struggling with how to aggregate the text value for use in the matrix.  What do you recommend?

RecentAction = 
    VAR __Table = 'SOS'
    VAR __MaxDate = MAXX(__Table, [Sample Date])
    VAR __ActionTable = FILTER(__Table, [Sample Date] = __MaxDate)
    VAR __Result = SELECTCOLUMNS(__ActionTable, [Action])
RETURN
    __Result

[Greg_Deckler]

@austinhammer So I would normally implement it as something like this:

RecentAction = 
    VAR __Table = 'SOS'
    VAR __MaxDate = MAXX(__Table, [Sample Date])
    VAR __ActionTable = FILTER(__Table, [Sample Date] = __MaxDate)
    VAR __Result = MAXX(__ActionTable, [Action])
RETURN
    __Result


RecentLab = 
    VAR __Table = 'SOS'
    VAR __MaxDate = MAXX(__Table, [Sample Date])
    VAR __ActionTable = FILTER(__Table, [Sample Date] = __MaxDate)
    VAR __Result = MAXX(__ActionTable, [Lab])
RETURN
    __Result