Need help with DAX query running out of resources

[nandanu]

Hi All,

Urgently need your help with a special case which is causing a DAX query for a measure to run out of resources. I've been stumped for days and don't know what I'm doing incorrectly.

Here are the details:

Dataset has a table with approx 250k rows. Subset is displayed here. First slicer filters all records where ExpiryDate falls in that range. Second slicer is to set an upper range for the ProcessedDate.

TransactionID	Batch	BatchYear	EffectiveDate	ExpiryDate	ProcessedDate	Status
245467	Batch201534	2022	9/16/2022	9/16/2023	9/16/2022	Lost
245466	Batch201534	2022	9/16/2022	9/16/2023	9/16/2022	Lost
245464	Batch201534	2022	9/16/2022	9/16/2023	9/16/2022	Lost
245463	Batch201534	2022	9/16/2022	9/16/2023	9/16/2022	Lost
245404	Batch201534	2022	9/16/2022	9/16/2023	9/16/2022	Bound
245401	Batch201534	2022	9/16/2022	9/16/2023	9/16/2022	Lost
245392	Batch201534	2022	4/1/2022	9/16/2022	9/14/2022	Bound
245385	Batch201534	2022	4/1/2022	9/16/2022	9/14/2022	Bound
230249	Batch201534	2022	4/1/2022	9/16/2022	4/1/2022	Bound
230230	Batch201534	2021	9/16/2021	9/16/2022	9/16/2021	Bound
215515	Batch201534	2020	9/16/2020	9/16/2021	9/21/2020	Bound
215506	Batch201534	2019	9/16/2019	9/16/2020	9/16/2019	Bound
215496	Batch201534	2018	9/16/2018	9/16/2019	9/24/2018	Bound
185668	Batch201534	2017	9/16/2017	9/16/2018	9/16/2017	Bound
185658	Batch201534	2016	9/16/2016	9/16/2017	9/16/2016	Bound
185647	Batch201534	2015	9/16/2015	9/16/2016	9/16/2015	Bound
234054	Batch111437	2021	10/5/2021	10/5/2028	10/6/2021	Lost
233845	Batch111437	2021	10/5/2021	10/5/2022	10/6/2021	Lost
229572	Batch111437	2021	10/5/2021	10/5/2022	10/6/2021	Bound
213124	Batch111437	2020	10/5/2020	10/5/2021	10/23/2020	Bound
213114	Batch111437	2019	10/5/2019	10/5/2020	10/5/2019	Bound
213105	Batch111437	2018	10/5/2018	10/5/2019	10/9/2018	Bound
182342	Batch111437	2017	10/5/2017	10/5/2018	10/12/2017	Bound
182332	Batch111437	2016	10/5/2016	10/5/2017	10/5/2016	Bound
182322	Batch111437	2015	10/5/2015	10/5/2016	10/5/2015	Bound
182312	Batch111437	2014	10/5/2014	10/5/2015	10/6/2014	Bound

The colored rows are the ones that fall in the expiry date range for the above slicer.  The max transaction id in that range is the ExpiringTransactionId for that Batch. The objective is to find the RenewalTransactionId for each Batch where it's the maximum Bound transaction greater than the ExpiringTransactionId or, if no Bound transaction is found then the minimum Lost transaction greater than the ExpiringTransactionId. See colored rows above for the two cases.

The measures are:

ExpiringTransactionId =

VAR startExpirationPeriod = FIRSTDATE(DateTable2[Date])

VAR endExpirationPeriod = LASTDATE(DateTable2[Date])

RETURN

    CALCULATE(

        MAX(Transaction[TransactionID]),

        Transaction[ExpiryDate] >= startExpirationPeriod && Transaction[ExpiryDate] <= endExpirationPeriod,

        Transaction[Status] = "Bound",

        REMOVEFILTERS(Transaction[BatchYear])

    )

RenewalTransactionId =

    VAR maxProcessedDate = LASTDATE(DateTable1[Date])

    VAR expiringTransactionId = [ExpiringTransactionId]

    // Find the max transaction id within the max processed date limit after the expiring transaction id where status is Bound

    VAR maxTransactionIdBound =

        CALCULATE(

            MAX(Transaction[TransactionID]),

            Transaction[ProcessedDate] <= maxProcessedDate,

            Transaction[TransactionID] > expiringTransactionId,

            Transaction[TransactionStatus] = "Bound",

            REMOVEFILTERS(Transaction[BatchYear])

        )

    // Find the min transaction id within the max processed date limit after the expiring transaction id where status is Lost

    VAR maxTransactionIdLost =

        CALCULATE(

            MIN(Transaction[TransactionID]),

            Transaction[ProcessedDate] <= maxProcessedDate,

            Transaction[TransactionID] > expiringTransactionId,                        

            Transaction[TransactionStatus] = "Lost",

            REMOVEFILTERS(Transaction[BatchYear])

        )

RETURN

    // If a valid Bound transaction id is found take that else go with the Lost transaction id.

    IF(

        NOT ISBLANK(maxTransactionIdBound),

        maxTransactionIdBound,

        maxTransactionIdLost

    )

The calculation for RenewalTransactionId when executed over the entire dataset of 250k rows results in the dreaded "Out of Resources" error. How can I rewrite it to be efficient in performance?

Thanks in advance for your help!

[ppm1]

A couple quick things to try:

1. IF.EAGER instead of IF

2. MAX instead of LASTDATE

Also see these videos for how to troubleshoot your slow visual and some design alternatives to consider. How many rows does your visual return? That is likely a big part of the problem.

https://www.youtube.com/watch?v=ScJVQoOWSqc&t=1s

https://www.youtube.com/watch?v=nNHSic2kDjM

Pat

[some_bih]

Hi @nandanu you should try to optimize your code. Best practice is to use DAX Studio to measure performance of storage and formula engine, overall timing ... with one code set and comparing it with changed scenario.

Firstly, without changing your code:

Usually number or rows are not issue for performance, it is big number of columns. Analyse your columns, if there are some unused columns simple remove them and check your performance.

Second: Try to create data on level which could be ok with Power Query transformation, like group or something

Third: Using FIRST/LAST DATE function could be replaced with Min/Max in some scenarios.

Great article explaining this could be found https://www.sqlbi.com/articles/understanding-the-difference-between-lastdate-and-max-in-dax/ 

and this one https://www.sqlbi.com/blog/marco/2013/10/22/difference-between-lastdate-and-max-for-semi-additive-me... (here you can find how to rewrite LASTDATE using Max).

Other: try to create data on level which could be ok with Power Query transformation

I hope this could help

[nandanu]

Hi @some_bih,

Thanks for your help with this issue. I have used DAX studio to track SE and FE load and it has helped somewhat. The xmSQL generated is beyond my expertise but it did help with optimizing the query by analyzing the cardinality of the comparisons (comparing IDs vs dates). 

Your suggestion about replacing FIRSTDATE/LASTDATE with MIN/MAX does seem to have helped a bit. But I still get the out of resources error when the report is run over the entire dataset. For now, we are getting around this issue by having users filter the dataset to a limited period that is relevant to the Expiring Between and As Of Date filters.