DAX Measure Optimization - Please help

Hi NRF95,

I hope you are doing well today ☺️❤️

So both Solutions A and B return correct results but from a performance and best practice DAX POV ,Solution B is strongest and more optimized approach

Let me Explain it for you

First Approach (A Solution)

• While A is a Good approach but it still introduces unnecessary overhead Like:

  • It relies on a CALCULATE inside SUMX which causes repeated context transitions (Not ideal for Performance)

  • The KPI filter is applied outside the iterator meaning it will reevaluate for each row group

  • In short it will produce more Formula Engine work than needed

Second Approach (B Solution) - Better Solution

• This Solution improves performance in several important ways like:

  • The KPI filter is pushed inside SUMMARIZECOLUMNS allowing it to be applied once and early

  • It avoids nested CALCULATE calls inside iterators

  • SUMMARIZECOLUMNS is well optimized by the engine (for grouping and measure evaluation)

  • This is best for reducing context transitions and improves Engine efficiency

• In Short Approach B is both cleaner and faster while doing the exact calculation logic

Final Recommendation:

• Do this if there is a relationship between DimKPI and FactProductivity this final version is more clean and well performed:

CY Periodical - OMNI - # Actual Covering Agents =
VAR _KPIKey = 271
RETURN
SUMX(
    SUMMARIZECOLUMNS(
        FactProductivity[FiscalMonth],
        FactProductivity[CustomerKey],
        KEEPFILTERS ( DimKPI[KPIKey] = _KPIKey ),
        "Covered",
        [CY Periodical Generic - Productivity - DCount LastEmployeeKey]
    ),
    [Covered]
)

• If there is no relationship between them then Solution B using TREATAS is the correct and optimized

Hi NRF95 ,
First, What your original measure is really doing?

Your original measure:

• Creates a virtual table grouped by
  • FiscalMonth × CustomerKey
• For each group, evaluates
  • DISTINCTCOUNTNOBLANK ( FSOLastEmployeeKey )
• Applies KPIKey = 271
• Then SUMX over the virtual table

So the expensive parts are:

1. CALCULATETABLE + SUMMARIZECOLUMNS
2. Nested CALCULATE
3. Row-by-row SUMX

Evaluation of your solutions

Solution A - Why it’s NOT optimal?

• Forces context transition inside SUMX
• DISTINCTCOUNT is re-evaluated row by row
• Filter is applied outside, not pushed into the grouping
• Works logically, but worst engine plan of the 3

Solution B - Why this is better?

• KPI filter pushed inside SUMMARIZECOLUMNS
• No nested CALCULATE per row
• Cleaner filter propagation
• Better storage engine utilization
• Solution B is clearly better than A

Best Optimized Version (Better than both):

You can remove the virtual column entirely and let the engine aggregate directly:

---DAX---
CY Periodical - OMNI - # Actual Covering Agents :=
CALCULATE (
SUMX (
SUMMARIZECOLUMNS (
FactProductivity[FiscalMonth],
FactProductivity[CustomerKey]
),
DISTINCTCOUNTNOBLANK ( FactProductivity[FSOLastEmployeeKey] )
),
DimKPI[KPIKey] = 271
)
---DAX---
Why this is the best?

• No nested CALCULATE
• No calculated column inside SUMMARIZE
• KPI filter applied once
• Storage Engine can optimize DISTINCTCOUNT
• Same logic, fewer formula engine calls

Solution A → logically correct, performance regression

Solution B → better, safe improvement

Final version above → best optimized, same logic, best engine plan

=================================================================
Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Jaywant Thorat | MCT | Data Analytics Coach
LinkedIn: https://www.linkedin.com/in/jaywantthorat/
Join #MissionPowerBIBharat = https://shorturl.at/5ViW9
#MissionPowerBIBharat
LIVE with Jaywant Thorat from 10 Jan 2026
8 Days | 8 Sessions | 1 hr daily | 100% Free

My suggestion would be to make a new meaure out of your calculate statement:

New Measure = 

CALCULATE (

[CY Periodical Generic - Productivity - DCount LastEmployeeKey],

KEEPFILTERS ( DimKPI[KPIKey] = _KPIKey )

Maybe also make a numeric parameter for your KPI key so you can use that value in multiple measures so you don't have to change it all the different places.

Once that new meausure is in the model, you can add it to your summarizecolumns statement without declaring the code in there.  My guess is that calculate statement inside of the summarizecolumns is what's slowing it down.