DAX measure in table running out of memory

Everton — Wed, 07 Dec 2022 05:42:55 GMT

Hi,

I have some DAX measures that are used in a table, they are running out of memory. Is there a better way to do this? I want to display this data in a table. What I want to do is measure the number of days between the latest date in the slicer to the invoice date. I have this measure to do this:

DaysSinceInvoice = VAR MaxDate = CALCULATE( MAX('Data DateDimension'[TheDate]), ALLSELECTED('Data DateDimension'[Date])) VAR Result = DATEDIFF(Min([InvoiceDate]), MaxDate, DAY) RETURN CONVERT(Result, INTEGER)

Then I have three measures that uses the above measure.

0To30Days = VAR Result = IF(Min('Data InvoiceFact'[Owing]) > 0 && [DaysSinceInvoice] >= 0 && [DaysSinceInvoice] <= 30, Min('Data InvoiceFact'[Owing], 0) Return Result 30To60Days = VAR Result = IF(Min('Data InvoiceFact'[Owing]) > 0 && [DaysSinceInvoice] >= 30 && [DaysSinceInvoice] <= 60, Min('Data InvoiceFact'[Owing], 0) Return Result 60To90Days = VAR Result = IF(Min('Data InvoiceFact'[Owing]) > 0 && [DaysSinceInvoice] >= 60 && [DaysSinceInvoice] <= 90, Min('Data InvoiceFact'[Owing], 0) Return Result

This is displayed in a table. Is there a more efficient way to do this? I am using a measure because I want to base the calculation on the date slicer selection.

Re: DAX measure in table running out of memory

johnt75 — Wed, 07 Dec 2022 10:19:54 GMT

You can try storing the data in variables so that the calculations only have to be done once, e.g.

0To30Days = VAR DaysSinceInvoice = [DaysSinceInvoice] VAR DaysOwing = MIN ( 'Data InvoiceFact'[Owing] ) VAR Result = IF ( DaysOwing > 0 && DaysSinceInvoice >= 0 && DaysSinceInvoice <= 30, DaysOwing, 0 ) RETURN Result

topic DAX measure in table running out of memory in DAX Commands and Tips

DAX measure in table running out of memory

Re: DAX measure in table running out of memory