topic Re: Finding a MAX value of a summarized table in DAX in DAX Commands and Tips

Finding a MAX value of a summarized table in DAX

hobosapien — Thu, 16 Jan 2025 02:29:38 GMT

I have the following DAX which resulted in the table below. I would like to see a result showing the max value in the summarized table. I attempted to wrap it in MAX, MAXX, and alternatively store the MAX'd table in a separate variable and then return that but failed.

Min Talk Time = VAR ct_TalkTime = SUMMARIZE('f_Call Metrics','f_Call Metrics'[Agent], "AVG Talk Time", AVERAGE('f_Call Metrics'[Average Handle Time])) RETURN ct_TalkTime

Agent	AVG Talk Time
Aubriella Maddox	0.008394
Connor Oliver	0.008659
Luke Rosales	0.010166
Jocelyn Vazquez	0.008448
Esme Mullen	0.007051
Sara Evans	0.00831
Elias Marshall	0.012287
Donovan Christian	0.008265

Re: Finding a MAX value of a summarized table in DAX

OwenAuger — Thu, 16 Jan 2025 03:03:41 GMT

Hi hobosapien

This is how I would suggest writing a measure Max time per Agent to return the max value of the average of Average Handle Time per agent:

Max Time per Agent = MAXX ( VALUES ( 'f_call Metrics'[Agent] ), CALCULATE ( AVERAGE( 'f_Call Metrics'[Average Handle Time] ) ) )

Alternatively, you could first create this measure:

Average Time = AVERAGE( 'f_Call Metrics'[Average Handle Time] )

then use it within Max Time per Agent:

Max Time per Agent = MAXX ( VALUES ( 'f_call Metrics'[Agent] ), [Average Time] )

Does this work for you?

Re: Finding a MAX value of a summarized table in DAX

hobosapien — Thu, 16 Jan 2025 12:45:10 GMT

That worked, thank you! Although I wonder why I can't reference the summarized table and find the MAX that way? Is there some limitation referencing a table that was created within the same measure?

Re: Finding a MAX value of a summarized table in DAX

OwenAuger — Thu, 16 Jan 2025 14:04:54 GMT

I'm glad it worked 🙂

You certainly can reference a summarized table if you like. I tend to avoid this approach where possible because the summarized table is materialized in memory when the measure is evaluated, which will have an impact on performance.

Firstly, a couple of things to note:

In general you should not add extended columns with the SUMMARIZE function itself. Instead, use ADDCOLUMNS ( SUMMARIZE ( ... ) ). See this article.
When using ADDCOLUMNS/SUMMARIZE, the extended column expressions must be wrapped in CALCULATE (context transition).
If there is a single grouping column specified within SUMMARIZE and it is from the same table as the first argument, you can just use VALUES with a single column argument (if there are no extended columns).
In other words, SUMMARIZE ( tbl, tbl[col] ) and VALUES ( tbl[col] ) are equivalent.

Taking the above into account, you could write your measure as:

Max Time per Agent = VAR AgentTime = ADDCOLUMNS ( VALUES ( 'f_call Metrics'[Agent] ), -- or SUMMARIZE ( 'f_call Metrics', 'f_call Metrics'[Agent] ) "AVG Talk Time", CALCULATE ( AVERAGE ( 'f_Call Metrics'[Average Handle Time] ) ) ) VAR MaxAgentTime = MAXX ( AgentTime, [AVG Talk Time] ) RETURN MaxAgentTime

If you did want to use SUMMARIZE itself to add columns (though I would not recommend this), it would look like this:

Max Time per Agent = VAR AgentTime = SUMMARIZE ( 'f_call Metrics', 'f_call Metrics'[Agent], "AVG Talk Time", AVERAGE ( 'f_Call Metrics'[Average Handle Time] ) -- CALCULATE not required ) VAR MaxAgentTime = MAXX ( AgentTime, [AVG Talk Time] ) RETURN MaxAgentTime

In both cases, you don't have to create the AgentTime variable. You could just move the AgentTime expression inside MAXX.

Regards