Forum Discussion
Helper VSurprisingly slow measure
Hi mike_asplin ,
Thanks for sharing the PBIX file.
Please try the following version of the measure. It keeps the same calculation logic as your original approach but removes the use of SUMMARIZE and ADDCOLUMNS, which are contributing to the performance issue:
AVERAGEX ( VALUES ( DateTable[MonthYear] ), SUMX ( VALUES ( Client[Client ID] ), CALCULATE ( MAX ( Client[FTE Prev 30 days] ) * [Distinct Paying Clients ex Canc] ) ) )This also avoids the “single value cannot be determined” error by explicitly resolving the client level FTE value.
Attached pbix file for reference.
Hope this helps.
Please reach out for further assistance.
Thank you.
Super UserYour 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
)
)
Helper VSorry throws an error single value ofr [FTE prev 30 days] in table client cannot be determined