Forum Discussion

fjjpeeters1976's avatar
fjjpeeters1976
Icon for Helper III rankHelper III
1 year ago

ALL() giving performance issues

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 ...
fjjpeeters1976's avatar
fjjpeeters1976
Icon for Helper III rankHelper III
1 year ago
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
  • v-aatheeque's avatar
    v-aatheeque
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi fjjpeeters1976 

    Total Split - BAE_BLS All (Fixed) =
    VAR Yr =
        SELECTEDVALUE ( 'Base Year'[Year] )

     

    -- Store values to be reused inside TREATAS
    VAR TrtValues = VALUES ( 'AllocationKey Man.Fee'[Entitycode] )

     

    -- Net Sales Total
    VAR full =
        CALCULATE (
            [Amount Adjusted],
            DimScenarioDetailed[Scen_Level1] = "FA",
            'Date'[Year] = Yr,
            DimAccountDetailed[Net Sales Total] = 1,
            TREATAS ( TrtValues, DimEntity[Entity_flat_NodeName] ),
            ALL ()
        )

     

    VAR BLS =
        CALCULATE (
            CALCULATE (
                [Amount Adjusted],
                DimScenarioDetailed[Scen_Level1] = "FA",
                'Date'[Year] = Yr,
                DimAccountDetailed[Net Sales Total] = 1,
                TREATAS ( TrtValues, DimEntity[Entity_flat_NodeName] ),
                ALL ()
            ),
            'AllocationKey Man.Fee'[BA-Allocation] = "BAE_BLS"
        )

     

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

     

    -- Last date of year
    VAR lastD =
        CALCULATE (
            LASTDATE ( 'Fact'[Date] ),
            FILTER ( ALL ( 'Date' ), 'Date'[Year] = Yr )
        )

     

    -- Balance Sheet Section
    VAR balancesheet =
        CALCULATE (
            [Amount Adjusted],
            DimScenarioDetailed[Scen_Level1] = "FA",
            DimAccountDetailed[BalanceSheet] = 1,
            TREATAS ( TrtValues, DimEntity[Entity_flat_NodeName] ),
            ALL (),
            'Date'[Date] = lastD
        )

     

    VAR balan =
        CALCULATE (
            CALCULATE (
                [Amount Adjusted],
                DimScenarioDetailed[Scen_Level1] = "FA",
                DimAccountDetailed[BalanceSheet] = 1,
                TREATAS ( TrtValues, DimEntity[Entity_flat_NodeName] ),
                ALL (),
                '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,
            DimAccountDetailed[P&L] = 1,
            TREATAS ( TrtValues, DimEntity[Entity_flat_NodeName] ),
            ALL ()
        )

     

    VAR prof =
        CALCULATE (
            CALCULATE (
                [Amount Adjusted],
                DimScenarioDetailed[Scen_Level1] = "FA",
                'Date'[Year] = Yr,
                DimAccountDetailed[P&L] = 1,
                TREATAS ( TrtValues, DimEntity[Entity_flat_NodeName] ),
                ALL ()
            ),
            '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


    Hope this helps !!

    • fjjpeeters1976's avatar
      fjjpeeters1976
      Icon for Helper III rankHelper III
      1 year ago

      Hi, when I incorporate your solution my number changes, so I guess the evaluation of the variable in the context is different from the evaluation in the part of the function itself.

       

      BR,

      Frank

      • v-aatheeque's avatar
        v-aatheeque
        Icon for Community Support rankCommunity Support
        1 year ago

        Hi fjjpeeters1976 
        Thanks for getting back!

        To help us understand the issue better, could you let us know which part of the calculation seems off  is it the NetResult, Bal, or ProResult? Also, are you testing the formula in a specific visual like a matrix or card, or looking at it from a total level?

         

        Let us know if there are any filters applied, such as year, entity, or scenario. Lastly, if possible, please share what result you were expecting and what value you’re seeing instead.

         

        This will help us verify whether it’s a context evaluation issue or something specific to your data model.