Measure to return the max value per category's text column in matrix

[rhcma]

Hello,

I have not had any luck with this so far. Basically I would like to show in a matrix column the text value for the largest Request ID per Record ID when the matrix is collasped. (Request ID is the primary key)

Sample data below, highlighted is the two highest Request IDs per Record ID and their First text per Record Id.

When I collaspe the the matrix the First text quick measure displays the first value A to Z (or last value if you use the Last quick measure). I would like the text to instead display the above highlighted largest request ID's text.

I hope that's clear, let me know any questions.

Record ID	Request ID	Text
100001	200013	An agreement description
100001	200072	Description of agreeement
100001	200007	Varied Description
100001	200023	Description again for agreement
100002	200083	New Agreement Description Agreement
100002	200091	Second description agreement
100003	200015	Varied Description Agreement
100004	200071	Post it note
100005	200047	Water bottle
100005	200082	Mouse

[speedramps]

Try this  ....

Answer = 
// get the max request ID for the line beinf displayed
VAR max_requestid = MAX(yourdata[Request ID])

// get the text for the max request ID 
VAR max_requestid_text =
CALCULATE(
    MAX(yourdata[Text]),
    yourdata[Request ID] = max_requestid
)
RETURN
// when the matrix is expanded display the row text value
// when the matic is collapse display the text for the max request ID 

IF(ISFILTERED(yourdata[Request ID]),
MAX(yourdata[Text]),
max_requestid_text)

Please click thumbs up because I have tried to help.

Then click [accept solution] if it works.

[MFelix · Accepted solution]

Hi @rhcma ,

Try the following measure:

Text Value = 
            VAR temptable = TOPN(1, SELECTCOLUMNS('Table', "ID",'Table'[Record ID],"Request",'Table'[Request ID],"Text",'Table'[Text]), [Request], DESC)
            Return
                MAXX(temptable, [Text])

[rhcma]

Thank you very much! That works in test I will post a follow up if I run into any issues!