Forum Discussion

fjjpeeters1976's avatar
fjjpeeters1976
Helper 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 ...
v-aatheeque's avatar
v-aatheeque
Community 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.

fjjpeeters1976's avatar
fjjpeeters1976
Helper III
1 year ago

Hi, thanks for your reply.
I put my old and the new measures on a card visual next to eachother to make sure they all have the same filters applied to them. There are filters on year, entity and some other criteria, but they are same for both.
Comparing the three parts of the calculation. In the new calculation logic both netresult and proresult are giving me exactly 25% which is equal to both factors that I multiply with (25%). So it feels like in the new situation the denominator and the numerator are the same number ending up at value 1 which is multiplied than with the factor.

Factor[Net sales factor] 
Factor[Conv.cost factor]

In the correct version the netresult should end up at 11% and the proresult at 10.28% totalling to 21.28%.

Regarding the balancesheet part, I have an issue with the uploaded datapart so both of them are giving me a blank value at the moment, but I assume this is more a dataload issue than a DAX issue. When the upload is corrected I can check this part as well.

 

Hope this clarifies it a bit. Love to hear your thoughts. Due to the size and confidentiality of the data I unfortunately cannot provide you with a data-extract.

 

BR,

Frank





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

    Hi fjjpeeters1976 

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

     

    -- Entities used for TREATAS
    VAR TrtValues = VALUES('AllocationKey Man.Fee'[Entitycode])

     

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

     

    VAR BLS =
        CALCULATE(
            [Amount Adjusted],
            DimScenarioDetailed[Scen_Level1] = "FA",
            DimAccountDetailed[Net Sales Total] = 1,
            'Date'[Year] = Yr,
            TREATAS(TrtValues, DimEntity[Entity_flat_NodeName]),
            '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,
            TREATAS(TrtValues, DimEntity[Entity_flat_NodeName]),
            'Date'[Date] = lastD
        )

     

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

     

    VAR prof =
        CALCULATE(
            [Amount Adjusted],
            DimScenarioDetailed[Scen_Level1] = "FA",
            DimAccountDetailed[P&L] = 1,
            'Date'[Year] = Yr,
            TREATAS(TrtValues, DimEntity[Entity_flat_NodeName]),
            'AllocationKey Man.Fee'[BA-Allocation] = "BAE_BLS"
        )

     

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

     

    -- Final
    RETURN
        NetResult + Bal + ProResult


    Hope this helps !!

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

      Hi fjjpeeters1976 

      Just checking in to see if the updated DAX measure provided earlier resolved the calculation discrepancy you mentioned. Were you able to test it?

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

        Hi fjjpeeters1976 

        I’m following up to check whether the updated DAX measure I shared earlier has helped address the calculation discrepancy you reported.

        Have you had a chance to test it in your report and verify if the results now align with your expected output?