Forum Discussion

austinhammer's avatar
austinhammer
Frequent Visitor
3 years ago
Solved

Matrix - text fields summarization

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 ...
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

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

austinhammer's avatar
austinhammer
Frequent Visitor
3 years ago

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's avatar
Greg_Deckler
Community Champion
3 years ago

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