ALL() giving performance issues

Hi fjjpeeters1976 ,

Your DAX measure's performance issues likely stem from two main sources: significant redundancy in your calculations and the inefficient use of the ALL() function. Your original code calculates very similar base numbers multiple times within the same measure. For example, the core logic in your full variable is nearly identical to the inner part of your BLS variable, forcing the DAX engine to perform the same complex work repeatedly. Additionally, ALL() is a very expensive, "brute force" function that removes all filters from a table, which is often more than necessary and can lead to slow data scans, especially within the complex filter context of a matrix.

A more performant and "clever" approach is to refactor the DAX to eliminate this redundancy and be more precise with filter removal. You can accomplish this by defining values used multiple times, like the selected year and the TREATAS logic, in **VAR**iables at the top so they are computed only once. The most critical optimization, however, is to replace the broad ALL() with the specific REMOVEFILTERS() function. This allows you to target only the columns that are actually on the rows or columns of your matrix visual, which is the correct way to calculate a ratio total without ignoring other important filters from slicers.

Here is the rewritten, more efficient version of your measure.

Total Split - BAE_BLS All (Optimized) =

-- 1. DEFINE CORE VARIABLES ONCE
VAR Yr = SELECTEDVALUE('Base Year'[Year])
VAR lastD = CALCULATE(
    MAX('Fact'[Date]),
    ALL('Date'), -- Ensures we get the last date of the year regardless of other date filters
    'Date'[Year] = Yr
)
VAR vEntityFilter = TREATAS(
    VALUES('AllocationKey Man.Fee'[Entitycode]),
    DimEntity[Entity_flat_NodeName]
)

-- 2. NET SALES CALCULATION
VAR TotalNetSales =
    CALCULATE(
        [Amount Adjusted],
        DimScenarioDetailed[Scen_Level1] = "FA",
        'Date'[Year] = Yr,
        DimAccountDetailed[Net Sales Total] = 1,
        vEntityFilter,
        -- IMPORTANT: Replace with the columns on your matrix axes!
        REMOVEFILTERS(DimAccountDetailed, DimEntity) 
    )
VAR SpecificNetSales =
    CALCULATE(
        [Amount Adjusted],
        DimScenarioDetailed[Scen_Level1] = "FA",
        'Date'[Year] = Yr,
        DimAccountDetailed[Net Sales Total] = 1,
        vEntityFilter,
        'AllocationKey Man.Fee'[BA-Allocation] = "BAE_BLS", -- Additional filter
        -- IMPORTANT: Use the same REMOVEFILTERS as above
        REMOVEFILTERS(DimAccountDetailed, DimEntity)
    )
VAR NetResult = DIVIDE(SpecificNetSales, TotalNetSales) * Factor[Net sales factor]

-- 3. BALANCE SHEET CALCULATION
VAR TotalBalanceSheet =
    CALCULATE(
        [Amount Adjusted],
        DimScenarioDetailed[Scen_Level1] = "FA",
        DimAccountDetailed[BalanceSheet] = 1,
        'Date'[Date] = lastD,
        vEntityFilter,
        -- IMPORTANT: Replace with the columns on your matrix axes!
        REMOVEFILTERS(DimAccountDetailed, DimEntity, DimScenarioDetailed[Scen_Level2])
    )
VAR SpecificBalanceSheet =
    CALCULATE(
        [Amount Adjusted],
        DimScenarioDetailed[Scen_Level1] = "FA",
        DimAccountDetailed[BalanceSheet] = 1,
        'Date'[Date] = lastD,
        vEntityFilter,
        'AllocationKey Man.Fee'[BA-Allocation] = "BAE_BLS", -- Additional filter
        -- IMPORTANT: Use the same REMOVEFILTERS as above
        REMOVEFILTERS(DimAccountDetailed, DimEntity, DimScenarioDetailed[Scen_Level2])
    )
VAR Bal = DIVIDE(SpecificBalanceSheet, TotalBalanceSheet) * Factor[Prod.asset factor]

-- 4. P&L CALCULATION
VAR TotalPL =
    CALCULATE(
        [Amount Adjusted],
        DimScenarioDetailed[Scen_Level1] = "FA",
        'Date'[Year] = Yr,
        DimAccountDetailed[P&L] = 1,
        vEntityFilter,
        -- IMPORTANT: Replace with the columns on your matrix axes!
        REMOVEFILTERS(DimAccountDetailed, DimEntity)
    )
VAR SpecificPL =
    CALCULATE(
        [Amount Adjusted],
        DimScenarioDetailed[Scen_Level1] = "FA",
        'Date'[Year] = Yr,
        DimAccountDetailed[P&L] = 1,
        vEntityFilter,
        'AllocationKey Man.Fee'[BA-Allocation] = "BAE_BLS", -- Additional filter
        -- IMPORTANT: Use the same REMOVEFILTERS as above
        REMOVEFILTERS(DimAccountDetailed, DimEntity)
    )
VAR ProResult = DIVIDE(SpecificPL, TotalPL) * Factor[Conv.cost factor]

-- 5. FINAL RESULT
VAR result = NetResult + Bal + ProResult
RETURN
result

For this optimized code to work correctly and yield the best performance, it is essential that you modify the REMOVEFILTERS() functions. You must replace the placeholder tables and columns I've used (e.g., DimAccountDetailed, DimEntity) with the actual fields that you have placed on the rows and columns of your matrix visual. This final step is crucial for tailoring the calculation to your specific report and achieving the correct results efficiently.

Best regards,