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 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?

 

3 Replies

    • 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