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