Surprisingly slow measure

mike_asplin Can you try the below DAX and see if it helps. ?

FTEs Purchased 1 =
AVERAGEX(
    ADDCOLUMNS(
        SUMMARIZECOLUMNS(
            DateTable[MonthYear],
            Client[Client ID],
            "FTE Contribution", Client[FTE Prev 30 days] * [Distinct Paying Clients ex Canc]
        ),
        "FTE", [FTE Contribution]
    ),
    [FTE])

Avoid nested SUMMARIZE/ADDCOLUMNS/SUMX—iterate directly over months or clients.

Move measures out of row context if possible.

Use VALUES or SUMMARIZECOLUMNS instead of SUMMARIZE + ADDCOLUMNS to reduce repeated calculations.

Aggregate at the month level first instead of calculating for each client within each month.

If this response was helpful, please accept it as a solution and give kudos to support other community member.

Regards,
Rufyda Rahma | MIE

Hi mike_asplin 

FTEs Purchased Optimized  =
VAR MonthlyData =
SUMMARIZECOLUMNS(
// Bring in the MonthYear for grouping
DateTable[MonthYear],
// Also bring in Client ID to get the correct filter for [Distinct Paying Clients ex Canc]
Client[Client ID],
// The filter from DateTable[MonthYear] is now active for the measure
"Weighted FTE",
VAR CurrentClientFTE = SELECTEDVALUE(Client[FTE Prev 30 days])
// This measure now evaluates in a context filtered by both Date[MonthYear] AND Client[ID]
VAR ClientsForMonth = [Distinct Paying Clients ex Canc]
RETURN
CurrentClientFTE * ClientsForMonth
)
// Now sum the weighted FTE for each month and then average
VAR MonthlyTotals =
ADDCOLUMNS(
VALUES(DateTable[MonthYear]),
"Monthly Total",
CALCULATE(
SUMX(
FILTER(MonthlyData, [MonthYear] = EARLIER([MonthYear])),
[Weighted FTE]
)
)
)
RETURN
IF(
NOT ISEMPTY(MonthlyTotals),
AVERAGEX(MonthlyTotals, [Monthly Total]),
BLANK()
)

Can you please check and confirm. Thank You!

Your measure is slow because it’s doing nested iterator work (AVERAGEX → ADDCOLUMNS → SUMX over a SUMMARIZE) and, crucially, it’s forcing [Distinct Paying Clients ex Canc] to be evaluated many times (once per month and per client row context you create). That “113ms alone” becomes 113ms × N evaluations.

A much faster pattern is: iterate clients once, and let the date/month context come from the visual (or an outer CALCULATE), instead of building a MonthYear table inside the measure.

Try this rewrite:

FTEs Purchased =
AVERAGEX (
    VALUES ( DateTable[MonthYear] ),
    VAR PayingClients = [Distinct Paying Clients ex Canc]
    RETURN
        SUMX (
            VALUES ( Client[Client ID] ),
            Client[FTE Prev 30 days] * PayingClients
        )
)

Sorry took a while to cut down the size of the data

Under Metrics/FTE are my previous attempts

[total FTE Purchased] calculates on the fly for every month but it is only marginally slower than

[FTEs Puchased new] which calcuates the last 30 days FTE as a column on client table and uses that for every month. Less accurate, but i thought it would be much quicker. 

[FTE purchased Optimized]is stuck up in ABV folder as cant move it 

File is here   https://www.dropbox.com/scl/fi/z3ml56s8habcc2k4q9py0/Trinity-PowerBI-test.pbix?rlkey=2q2qnqy4qliyofyv8qbz72av7&dl=0

Thanks for any advice

Hi mike_asplin ,
Just wanted to check if any of the responses provided were helpful. If further assistance is needed, please reach out.
Thank you.