how to make a DAX expression simpler

[Anonymous]

Hi,

I have a table of ~ 70.000 rows, which contains service actions from the past 18 months. I wrote a DAX expression to calculate for each row when the next service action for the same serial number happened. It works, but I keep running into memory allocation failures since I added it to my data model, so I am searching for an alternative way to do the same calculation in a more efficient way.

My current expression:

Next SA = Calculate(

    MIN('D Customer Reporting'[Work Order Created On]),

    FILTER('D Customer Reporting', 'D Customer Reporting'[Serial Number] = earlier('D Customer Reporting'[Serial Number])),

    FILTER('D Customer Reporting', 'D Customer Reporting'[Work Order Created On] > earlier('D Customer Reporting'[Work Order Completed On Date])),

    Filter('D Customer Reporting', 'D Customer Reporting'[Incident Type] in {"X", "Z", "Y"}))

Any idea how to do that?

thanks in advance!

[tamerj1 · Accepted solution]

Hi @Anonymous 

You may try

Next SA =
MINX (
    CALCULATETABLE (
        'D Customer Reporting',
        ALLEXCEPT (
            'D Customer Reporting',
            'D Customer Reporting'[Serial Number],
            'D Customer Reporting'[Work Order Created On]
        ),
        'D Customer Reporting'[Incident Type] IN { "X", "Z", "Y" }
    ),
    'D Customer Reporting'[Work Order Created On]
)

[Greg_Deckler]

@Anonymous This may or may not help. Note, you should structure your FILTER statements so that you filter out the most rows first. For example, if filtering to x y and z filters out more records rather than the date filter then that should come first.

Next SA = 
  VAR __SerialNumber = MAX('D Customer Reporting'[Serial Number])
  VAR __WorkCompleted = MAX('D Customer Reporting'[Work Order Completed On Date])
RETURN
CALCULATE(
    MIN('D Customer Reporting'[Work Order Created On]),
    FILTER('D Customer Reporting', 'D Customer Reporting'[Serial Number] = __SerialNumber) && 
    'D Customer Reporting'[Work Order Created On] > __WorkCompleted && 
    'D Customer Reporting'[Incident Type] in {"X", "Z", "Y"})
)

Another way:
Next SA = Calculate(
  VAR __SerialNumber = MAX('D Customer Reporting'[Serial Number])
  VAR __WorkCompleted = MAX('D Customer Reporting'[Work Order Completed On Date])
  VAR __Table = FILTER('D Customer Reporting', 'D Customer Reporting'[Serial Number] = __SerialNumber)
  VAR __Table1 = FILTER(__Table, [Work Order Created On] > __WorkCompleted)
  VAR __Table2 = FILTER(__Table1, [Incident Type] in {"X", "Z", "Y"})
RETURN
  MINX(__Table2, [Work Order Created On])