DAX Measure Optimization - Please help

[NRF95]

Hi All.

Could you please help me optimize this measure, the goal is an optimized version of this measure that improves performance while maintaining the same calculation logic:

Measure to be optimized: 

CY Periodical - OMNI - # Actual Covering Agents =

VAR _KPIKey = 271

VAR _Table =

CALCULATETABLE (

SUMMARIZECOLUMNS (

FactProductivity[FiscalMonth],

FactProductivity[CustomerKey],

"@Covered Outlets",

CALCULATE (

[CY Periodical Generic - Productivity - DCount LastEmployeeKey],

KEEPFILTERS ( DimKPI[KPIKey] = _KPIKey )

)

)

)

RETURN

SUMX ( _Table, [@Covered Outlets] )

So far I came to those two as solution for optimization:

Solution A:

CY Periodical - OMNI - # Actual Covering Agents =

VAR _KPIKey = 271

RETURN

CALCULATE (

SUMX (

SUMMARIZECOLUMNS (

FactProductivity[FiscalMonth],

FactProductivity[CustomerKey]

),

CALCULATE (

[CY Periodical Generic - Productivity - DCount LastEmployeeKey]

)

),

KEEPFILTERS ( DimKPI[KPIKey] = _KPIKey )

)

Solution B:

CY Periodical - OMNI - # Actual Covering Agents =

VAR _KPIKey = 271

RETURN

SUMX (

SUMMARIZECOLUMNS (

FactProductivity[FiscalMonth],

FactProductivity[CustomerKey],

KEEPFILTERS ( TREATAS ( { _KPIKey }, DimKPI[KPIKey] ) ),

"Covered",

[CY Periodical Generic - Productivity - DCount LastEmployeeKey]

),

[Covered]

)

Do you think any of my Solutions would provide an optimized version of this measure that improves performance while maintaining the same calculation logic. ? I have tested both Options and they work great in terms of whether the data is correct in my report, but the question is if there is even better solution or which of the two is the best in terms of the optimization that I am looking for.

This is something that my Boss gave me as a task and the help would be much appreciated!

EDIT: I forgot to put the measure which is referenced in the measure that needs to be optimized, I believe it would be helpful, here it is:

CY Periodical Generic - Productivity - DCount LastEmployeeKey = DISTINCTCOUNTNOBLANK ( FactProductivity[FSOLastEmployeeKey] )

[Jaywant-Thorat]

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

[Ahmed-Elfeel]

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

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[NRF95 · Accepted solution]

Hi Sir, Thank you for your reply! There is indeed a relationship between DimKPI and FactProductivity via key "KPIKey". However, when I use the version that you suggested I received the following error "A single value for column 'KPIKey' in table 'DimKPI' cannot be determined. This can happen when a measure or function formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

This is fine though, I will use the TRETAS measure that you confirmed is definitely the best alternative to the original measure. Thanks a lot again!

[Ahmed-Elfeel]

Hi @NRF95,

if my reply helps, then I would appreciate mark it as the solution to help the other members find it more quickly.

[d_m_LNK]

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.