ALL() giving performance issues

[fjjpeeters1976]

hi all,

I have the following function and it gives me the correct numbers. I had to add the ALL() condition everywhere to give me the correct number in the filtercontect of a matrix, however it makes everything very slow.

I tried to put the ALL() condition in the return statement but it does not give me the correct result.

Is there a more clever way of doing this?


Total Split - BAE_BLS All =

VAR Yr = SELECTEDVALUE('Base Year'[Year])

VAR full =

    CALCULATE(

        [Amount Adjusted],

        DimScenarioDetailed[Scen_Level1] = "FA",

        'Date'[Year] = Yr,

        DimAccountDetailed[Net Sales Total] = 1,

        TREATAS(

            VALUES('AllocationKey Man.Fee'[Entitycode]),

            DimEntity[Entity_flat_NodeName]

        ), all()

    )

VAR BLS =

    CALCULATE(

        CALCULATE(

            [Amount Adjusted],

            DimScenarioDetailed[Scen_Level1] = "FA",

            'Date'[Year] = Yr,

            DimAccountDetailed[Net Sales Total] = 1,

            TREATAS(

                VALUES('AllocationKey Man.Fee'[Entitycode]),

                DimEntity[Entity_flat_NodeName]

            ), all()

        ),

        'AllocationKey Man.Fee'[BA-Allocation] = "BAE_BLS"

    )

VAR NetResult = DIVIDE(BLS, full) * Factor[Net sales factor]

-- Balance Sheet Section

VAR lastD = CALCULATE(LASTDATE('Fact'[Date]), FILTER(ALL('Date'), 'Date'[Year]=Yr))

VAR balancesheet =

    CALCULATE(

        [Amount Adjusted],

        DimScenarioDetailed[Scen_Level1] = "FA",

        DimAccountDetailed[BalanceSheet] = 1, REMOVEFILTERS(DimScenarioDetailed[Scen_Level2]), REMOVEFILTERS(DimEntity[Entity_flat_NodeName]),

        TREATAS(

            VALUES('AllocationKey Man.Fee'[Entitycode]),

            DimEntity[Entity_flat_NodeName]

        ),

        'Date'[Date]=lastD

    )

VAR balan =

    CALCULATE(

        CALCULATE(

            [Amount Adjusted],

            DimScenarioDetailed[Scen_Level1] = "FA",

            DimAccountDetailed[BalanceSheet] = 1, all(),

            TREATAS(

                VALUES('AllocationKey Man.Fee'[Entitycode]),

                DimEntity[Entity_flat_NodeName]

            ),

           'Date'[Date]=lastD

        ),

        'AllocationKey Man.Fee'[BA-Allocation] = "BAE_BLS"

    )

VAR Bal = DIVIDE(balan, balancesheet) * Factor[Prod.asset factor]

-- P&L Section

VAR profit =

    CALCULATE(

        [Amount Adjusted],

        DimScenarioDetailed[Scen_Level1] = "FA",

        'Date'[Year] = Yr, all(),

        DimAccountDetailed[P&L] = 1,

        TREATAS(

            VALUES('AllocationKey Man.Fee'[Entitycode]),

            DimEntity[Entity_flat_NodeName]

        )

    )

VAR prof =

    CALCULATE(

        CALCULATE(

            [Amount Adjusted],

            DimScenarioDetailed[Scen_Level1] = "FA",

            'Date'[Year] = Yr, all(),

            DimAccountDetailed[P&L] = 1,

            TREATAS(

                VALUES('AllocationKey Man.Fee'[Entitycode]),

                DimEntity[Entity_flat_NodeName]

            )

        ),

        'AllocationKey Man.Fee'[BA-Allocation] = "BAE_BLS"

    )

VAR ProResult = DIVIDE(prof, profit) * Factor[Conv.cost factor]

-- Final Result

VAR result = NetResult + Bal + ProResult

RETURN

result

[fjjpeeters1976]

Hi, thank you for your reply. I wanted to step by step update my Dax formula to make sure I was doing everything correct. First thing I did was moving the treatas part into a variable. However this changed the outcome of my measure. Please see below the new and the old measure.

NEW Version
Total Split - BAE_BLS All newtry =

VAR Yr =

    SELECTEDVALUE ( 'Base Year'[Year] )

VAR Trt = TREATAS (

                VALUES ( 'AllocationKey Man.Fee'[Entitycode] ),

                DimEntity[Entity_flat_NodeName]

            )

VAR full =

    CALCULATE (

        [Amount Adjusted],

        DimScenarioDetailed[Scen_Level1] = "FA",

        'Date'[Year] = Yr,

        DimAccountDetailed[Net Sales Total] = 1,

        Trt,

        ALL ()

    )

VAR BLS =

    CALCULATE (

        CALCULATE (

            [Amount Adjusted],

            DimScenarioDetailed[Scen_Level1] = "FA",

            'Date'[Year] = Yr,

            DimAccountDetailed[Net Sales Total] = 1,

            Trt,

            ALL ()

        ),

        'AllocationKey Man.Fee'[BA-Allocation] = "BAE_BLS"

    )

VAR NetResult =

    DIVIDE ( BLS, full ) * Factor[Net sales factor] -- Balance Sheet Section

VAR lastD =

    CALCULATE (

        LASTDATE ( 'Fact'[Date] ),

        FILTER ( ALL ( 'Date' ), 'Date'[Year] = Yr )

    )

VAR balancesheet =

    CALCULATE (

        [Amount Adjusted],

        DimScenarioDetailed[Scen_Level1] = "FA",

        DimAccountDetailed[BalanceSheet] = 1,

        all(),

       Trt,

        'Date'[Date] = lastD

    )

VAR balan =

    CALCULATE (

        CALCULATE (

            [Amount Adjusted],

            DimScenarioDetailed[Scen_Level1] = "FA",

            DimAccountDetailed[BalanceSheet] = 1,

            ALL (),

            Trt,

            'Date'[Date] = lastD

        ),

        'AllocationKey Man.Fee'[BA-Allocation] = "BAE_BLS"

    )

VAR Bal =

    DIVIDE ( balan, balancesheet ) * Factor[Prod.asset factor] -- P&L Section

VAR profit =

    CALCULATE (

        [Amount Adjusted],

        DimScenarioDetailed[Scen_Level1] = "FA",

        'Date'[Year] = Yr,

        ALL (),

        DimAccountDetailed[P&L] = 1,

        Trt

    )

VAR prof =

    CALCULATE (

        CALCULATE (

            [Amount Adjusted],

            DimScenarioDetailed[Scen_Level1] = "FA",

            'Date'[Year] = Yr,

            ALL (),

            DimAccountDetailed[P&L] = 1,

            Trt

        ),

        'AllocationKey Man.Fee'[BA-Allocation] = "BAE_BLS"

    )

VAR ProResult =

    DIVIDE ( prof, profit ) * Factor[Conv.cost factor] -- Final Result

VAR result = NetResult + Bal + ProResult

RETURN

    result

OLD Version
Total Split - BAE_BLS All =

VAR Yr =

    SELECTEDVALUE ( 'Base Year'[Year] )

VAR full =

    CALCULATE (

        [Amount Adjusted],

        DimScenarioDetailed[Scen_Level1] = "FA",

        'Date'[Year] = Yr,

        DimAccountDetailed[Net Sales Total] = 1,

        TREATAS (

            VALUES ( 'AllocationKey Man.Fee'[Entitycode] ),

            DimEntity[Entity_flat_NodeName]

        ),

        ALL ()

    )

VAR BLS =

    CALCULATE (

        CALCULATE (

            [Amount Adjusted],

            DimScenarioDetailed[Scen_Level1] = "FA",

            'Date'[Year] = Yr,

            DimAccountDetailed[Net Sales Total] = 1,

            TREATAS (

                VALUES ( 'AllocationKey Man.Fee'[Entitycode] ),

                DimEntity[Entity_flat_NodeName]

            ),

            ALL ()

        ),

        'AllocationKey Man.Fee'[BA-Allocation] = "BAE_BLS"

    )

VAR NetResult =

    DIVIDE ( BLS, full ) * Factor[Net sales factor] -- Balance Sheet Section

VAR lastD =

    CALCULATE (

        LASTDATE ( 'Fact'[Date] ),

        FILTER ( ALL ( 'Date' ), 'Date'[Year] = Yr )

    )

VAR balancesheet =

    CALCULATE (

        [Amount Adjusted],

        DimScenarioDetailed[Scen_Level1] = "FA",

        DimAccountDetailed[BalanceSheet] = 1,

        all(),

        TREATAS (

            VALUES ( 'AllocationKey Man.Fee'[Entitycode] ),

            DimEntity[Entity_flat_NodeName]

        ),

        'Date'[Date] = lastD

    )

VAR balan =

    CALCULATE (

        CALCULATE (

            [Amount Adjusted],

            DimScenarioDetailed[Scen_Level1] = "FA",

            DimAccountDetailed[BalanceSheet] = 1,

            ALL (),

            TREATAS (

                VALUES ( 'AllocationKey Man.Fee'[Entitycode] ),

                DimEntity[Entity_flat_NodeName]

            ),

            'Date'[Date] = lastD

        ),

        'AllocationKey Man.Fee'[BA-Allocation] = "BAE_BLS"

    )

VAR Bal =

    DIVIDE ( balan, balancesheet ) * Factor[Prod.asset factor] -- P&L Section

VAR profit =

    CALCULATE (

        [Amount Adjusted],

        DimScenarioDetailed[Scen_Level1] = "FA",

        'Date'[Year] = Yr,

        ALL (),

        DimAccountDetailed[P&L] = 1,

        TREATAS (

            VALUES ( 'AllocationKey Man.Fee'[Entitycode] ),

            DimEntity[Entity_flat_NodeName]

        )

    )

VAR prof =

    CALCULATE (

        CALCULATE (

            [Amount Adjusted],

            DimScenarioDetailed[Scen_Level1] = "FA",

            'Date'[Year] = Yr,

            ALL (),

            DimAccountDetailed[P&L] = 1,

            TREATAS (

                VALUES ( 'AllocationKey Man.Fee'[Entitycode] ),

                DimEntity[Entity_flat_NodeName]

            )

        ),

        'AllocationKey Man.Fee'[BA-Allocation] = "BAE_BLS"

    )

VAR ProResult =

    DIVIDE ( prof, profit ) * Factor[Conv.cost factor] -- Final Result

VAR result = NetResult + Bal + ProResult

RETURN

    result

[DataNinja777]

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,