Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Everton
Helper I
Helper I

DAX measure in table running out of memory

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. 

1 REPLY 1
johnt75
Super User
Super User

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors