Forum Discussion

hari127's avatar
hari127
Frequent Visitor
5 years ago

$Expr0 := CallbackDataID

The following code is rewritten to eliminate the callbackdataid but it increased SE Queries and decreased SE Cache and overall performance degraded. Is callbackdataid good or bad,  Is this the correct method to eliminate callbackdataid? Why performance is degraded?

CALCULATE(
SUMX(FILTER('AccountTransactions',(RELATED(Accounts[Account Group])=1 || RELATED(Accounts[Account Group])=2)),
if(RELATED(Accounts[Account Group])=1,[Debit]-[Credit],[Credit]-[Debit])),


FILTER (
ALL ( Dates[Date] ),
Dates[Date]<= MAX ( 'Dates'[Date])
)
)

---------------new code to eliminate callbackdataid

CALCULATE(
SUMX(FILTER('AccountTransactions',RELATED(Accounts[Account Group])=1),
[Debit]-[Credit]),

FILTER (
ALL ( Dates[Date] ),
Dates[Date]<= MAX ( 'Dates'[Date])
)
)
+
CALCULATE(
SUMX(FILTER('AccountTransactions',RELATED(Accounts[Account Group])=2),
[Credit]-[Debit]),

FILTER (
ALL ( Dates[Date] ),
Dates[Date]<= MAX ( 'Dates'[Date])
)
)

6 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    5 years ago

    Performance tuning is highly situational. Eliminating callbackdataid isn't always a good idea. It depends. Your second version requires iterating through 'AccountTransaction' twice instead of just once.

     

    I'm not sure this will work but it may give you an idea of another path to try:

    CALCULATE (
    CALCULATE ( [Debit] - [Credit], Accounts[Account Group] = 1 ) +
    CALCULATE ( [Credit] - [Debit], Accounts[Account Group] = 2 ),
    FILTER ( ALL ( Dates[Date] ), Dates[Date] <= MAX ( 'Dates'[Date] ) )
)

     

    • hari127's avatar
      hari127
      Frequent Visitor
      5 years ago

      Thank you very much for the support. The following are the results using three difference versions

      CALCULATE(
      SUMX(FILTER('AccountTransactions',(RELATED(Accounts[Account Group])=1 || RELATED(Accounts[Account Group])=2)),
      if(RELATED(Accounts[Account Group])=1,[Debit]-[Credit],[Credit]-[Debit])),


      FILTER (
      ALL ( Dates[Date] ),
      Dates[Date]<= MAX ( 'Dates'[Date])
      )
      )

       

       

      CALCULATE(
      CALCULATE(
      SUMX(FILTER('AccountTransactions',RELATED(Accounts[Account Group])=1),
      [Debit]-[Credit])
      )
      +
      CALCULATE(
      SUMX(FILTER('AccountTransactions',RELATED(Accounts[Account Group])=2),
      [Credit]-[Debit])),

      FILTER (
      ALL ( Dates[Date] ),
      Dates[Date]<= MAX ( 'Dates'[Date])
      )
      )

       

      Made changes to the model to include account group in the same table

      CALCULATE(
      CALCULATE(
      SUMX(FILTER('AccountTransactions',[Account Group]=1),
      [Debit]-[Credit])
      )
      +
      CALCULATE(
      SUMX(FILTER('AccountTransactions',[Account Group]=2),
      [Credit]-[Debit])),

      FILTER (
      ALL ( Dates[Date] ),
      Dates[Date]<= MAX ( 'Dates'[Date])
      )
      )

       

       

  • daxer-almighty's avatar
    daxer-almighty
    Solution Sage
    5 years ago

    If you want to have fast DAX, then never ever call a measure when iterating a fact table row by row.

     

    • hari127's avatar
      hari127
      Frequent Visitor
      5 years ago

      Did you find any measure in my code?

      • daxer-almighty's avatar
        daxer-almighty
        Solution Sage
        5 years ago

        I use THESE conventions to interpret DAX code when I read it. If you don't follow this golden standard, then you should not be surprised that people do not understand your code.