Forum Discussion

krish42's avatar
krish42
Frequent Visitor
6 months ago
Solved

DAX Query help

Hello,     I have 2 tables in the model 1. CUSTTRANS  RECID, ACCOUNTNUM, TANSDATE, AMOUNT, EXCHADJUSTMENT, TANSTYPE 2. CUSTSETTLEMENT TRANSRECID, TRANSDATE, EXCHADJUSTMENT, SETTLEAMOUNT 3. Cal...
  • krish42's avatar
    krish42
    6 months ago

    Thanks for the script.

    Though not exactly what I want but has given me an idea and I fixed the measure with RELATEDTABLE() function. Its giving the result and the performance is also good

Olufemi7's avatar
Olufemi7
Super User
6 months ago

Hello krish42, 

The resource issue is caused by using [Settlement Exch Adj] inside SUMX over CustTrans.

When a measure is evaluated inside an iterator, DAX performs a context transition and may repeatedly scan the related table for every row. With large tables this can exhaust resources.

Microsoft documentation:

Row context and filter context in DAX
https://learn.microsoft.com/dax/row-context-and-filter-context-in-dax

CALCULATE function (context transition)
https://learn.microsoft.com/dax/calculate-function-dax

SUMX function
https://learn.microsoft.com/dax/sumx-function-dax

To resolve this, avoid calling settlement measures inside SUMX.
Instead, calculate the exchange adjustment per transaction using the relationship.

Result Amount

Result Amount :=
VAR AgedDebtDate = MAX ( 'Calendar'[Date] )
RETURN
CALCULATE (
    SUMX (
        CustTrans,
        VAR BaseAmount = CustTrans[AMOUNTMST]
        VAR TransExch = CustTrans[EXCHADJUSTMENT]
        VAR SettlementExch =
            CALCULATE (
                SUM ( CustSettlements[EXCHADJUSTMENT] ),
                CustSettlements[TRANSDATE] <= AgedDebtDate
            )
        VAR FinalExch =
            IF (
                TransExch = 0 && SettlementExch <> 0,
                SettlementExch,
                TransExch
            )
        RETURN
            BaseAmount + FinalExch
    ),
    CustTrans[TRANSDATE] <= AgedDebtDate
)


Settlement Amount

Settlement Amount :=
VAR AgedDebtDate = MAX ( 'Calendar'[Date] )
RETURN
CALCULATE (
    SUM ( CustSettlements[SETTLEAMOUNTMST] ),
    CustSettlements[TRANSDATE] <= AgedDebtDate
)


Final Balance

Balance GBP :=
ROUND (
    [Result Amount] - [Settlement Amount],
    2
)

Why this works:

• The settlement exchange adjustment is evaluated within the transaction row context via the relationship
• No external measure is called inside the iterator
• Date filtering is applied correctly
• Eliminates repeated scans of CustSettlements

This follows Microsoft guidance on context transition and iterator performance and should resolve the resource error.