Finding a MAX value of a summarized table in DAX

[hobosapien]

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

[OwenAuger · Accepted solution]

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?

[hobosapien]

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?

[OwenAuger]

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