topic Re: Matrix - text fields summarization in DAX Commands and Tips

Matrix - text fields summarization

austinhammer — Wed, 30 Aug 2023 16:33:00 GMT

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?

Re: Matrix - text fields summarization

Greg_Deckler — Wed, 30 Aug 2023 17:00:57 GMT

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

Re: Matrix - text fields summarization

austinhammer — Wed, 30 Aug 2023 17:38:52 GMT

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

Re: Matrix - text fields summarization

Greg_Deckler — Wed, 30 Aug 2023 20:41:20 GMT

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