DAX Query help

[krish42]

Hello,

  I have 2 tables in the model

1. CUSTTRANS

 RECID, ACCOUNTNUM, TANSDATE, AMOUNT, EXCHADJUSTMENT, TANSTYPE

2. CUSTSETTLEMENT

TRANSRECID, TRANSDATE, EXCHADJUSTMENT, SETTLEAMOUNT

3. Calendar

  Date, Year, Month...

Relationship between these 2 tables is CUSTTRANS.RECID -> CUSTSETTLEMENT.TRANSRECID (one-to-many)

CUSTTRAMS.TRANSDATE -> Calendar.Date

I need a DAX query between these two tables to get the balance  as of a date.

EXCHADJUSTMENT in CUSTTRANSTABLE is not always populated. If it is zero and the CUSTSETTLEMENT.EXCHADJUSTMENT is not, I need to get the EXCHADJUSTMENT from CUSTSETTLEMENT else CUSTTRANS.EXCHADJUSTMENT

My DAX measures are as below

Settlement Exch Adj =
var AgedDebtDate = MAX('Calendar'[Date])
var ExchAdj = CALCULATE(SUM(CustSettlements[EXChADJUSTMENT]),CustSettlements[TRANSDATE] <= AgedDebtDate)
RETURN ExchAdj

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

Result AmountMST =
var AgedDebtDate = max('Calendar'[Date])
var AmountMST = SUMX (
'CustTrans',
SWITCH (
TRUE (),
'CustTrans'[TRANSTYPE] = 9, 'CustTrans'[AMOUNTMST],
CustTrans[EXCHADJUSTMENT] = 0 && [Settlement Exch Adj] <> 0,CustTrans[AMOUNTMST] - [Settlement Exch Adj],
CustTrans[AMOUNTMST] + CustTrans[EXCHADJUSTMENT]
)
)
RETURN AmountMST

Balance GBP (On Settlements) = ROUND(CALCULATE([Result AmountMST] - [Settlement Amount GBP]),2)

The balance query is taking all the available resources and report showing error.

Could anyone help to resolve the query. I am not expert in DAX.

[v-nmadadi-msft]

Hi @krish42 

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

[v-nmadadi-msft]

Hi @krish42 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[Olufemi7]

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.

[v-nmadadi-msft]

Hi @krish42  ,
Thanks for reaching out to the Microsoft Fabric Community forum.

The error is occurring because the DAX measure is forcing the engine to generate and process a very large intermediate result set during query execution, which exceeds the 15MB per-query data limit enforced by the Tabular engine.

As an immediate workaround, you can try reducing the dataset being evaluated before running the measure. For example:

• Apply a date filter (e.g., specific month or year) in the report using slicers.
• Filter by a specific AccountNum or a smaller customer subset.
• Use report-level or page-level filters to limit the number of transactions loaded into the visual.
• Temporarily test the measure on a smaller date range to confirm it works correctly.

I hope this information helps. Please do let us know if you have any further queries.
Thank you

[Kedar_Pande]

@krish42 

Result AmountMST = 
SUMX(
FILTER(CustTrans, CustTrans[TANSDATE] <= MAX('Calendar'[Date])),
VAR SettleAdj = RELATED(CustSettlements[EXCHADJUSTMENT])
RETURN
SWITCH(TRUE(),
CustTrans[TRANSTYPE] = 9, CustTrans[AMOUNTMST],
CustTrans[EXCHADJUSTMENT] = 0 && SettleAdj <> 0, CustTrans[AMOUNTMST] - SettleAdj,
CustTrans[AMOUNTMST] + CustTrans[EXCHADJUSTMENT]
)
)

If this answer helped, please click 👍 or Accept as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

[krish42 · Accepted solution]

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

[FBergamaschi]

Hi @krish42,

Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Alternatively, you can share your .pbix via some cloud service and paste the link here. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Need help uploading data? click here

Want faster answers? click here

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI