topic $Expr0 := CallbackDataID in DAX Commands and Tips

$Expr0 := CallbackDataID

hari127 — Fri, 04 Jun 2021 19:08:04 GMT

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])
)
)

Re: $Expr0 := CallbackDataID

AlexisOlson — Fri, 04 Jun 2021 20:07:19 GMT

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] ) ) )

Re: $Expr0 := CallbackDataID

hari127 — Sat, 05 Jun 2021 05:57:08 GMT

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])
)
)

Re: $Expr0 := CallbackDataID

daxer-almighty — Sat, 05 Jun 2021 16:23:42 GMT

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

Re: $Expr0 := CallbackDataID

hari127 — Sun, 06 Jun 2021 08:42:04 GMT

Did you find any measure in my code?

Re: $Expr0 := CallbackDataID

daxer-almighty — Sun, 06 Jun 2021 14:36:12 GMT

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.

Re: $Expr0 := CallbackDataID

hari127 — Sun, 06 Jun 2021 16:25:21 GMT

Thanks