Need help to optimize complex dax measure formula

[Mohan128256]

Greetings @rajendraongole1 , @Ritaf1983 , @SamWiseOwl @Greg_Deckler @lbendlin @Ashish_Mathur , @amitchandak 

I have a measure which calcualtes the before and new values within a range of dates selected for each

• Sales order id
• Product ID

For ex: - 

Input Column - Customer Reference

within a date range selected (snapshot date column), i am trying to calculated the BEFORE value using below DAX.

Customer Reference (H) Before = 
VAR _BaseFilter =
    FILTER(
        ALLSELECTED('Sales Orders Snapshots'),
        'Sales Orders Snapshots'[Legal Entity (H)] = "400" &&
        'Sales Orders Snapshots'[Original Legal Entity (L)] <> "400" &&
        'Sales Orders Snapshots'[Original Order Type (L)] = "Sales order"
    )

 VAR _MinDate =
    CALCULATETABLE(
    ADDCOLUMNS(
        SUMMARIZE(
            'Sales Orders Snapshots',
            'Sales Orders Snapshots'[Sales Order ID (H)],
            'Sales Orders Snapshots'[Product ID (L)]
        ),
        "@SnapDate", MIN('Sales Orders Snapshots'[SnapDate])
    ),
    _BaseFilter
    )
	
VAR _FilterSnap =
    TREATAS(
       _MinDate,
        'Sales Orders Snapshots'[Sales Order ID (H)],
        'Sales Orders Snapshots'[Product ID (L)],
        'Sales Orders Snapshots'[SnapDate]
    )
VAR _Result =
    CALCULATE(
        MAX('Sales Orders Snapshots'[Customer Reference (H)]),
        ALLEXCEPT(
            'Sales Orders Snapshots',
            'Sales Orders Snapshots'[Sales Order ID (H)],
            'Sales Orders Snapshots'[Product ID (L)]
        ),
        'Sales Orders Snapshots'[Legal Entity (H)] = "400",
        'Sales Orders Snapshots'[Original Legal Entity (L)] <> "400",
        'Sales Orders Snapshots'[Original Order Type (L)] = "Sales order",
        KEEPFILTERS(_FilterSnap)
    )
RETURN _Result

This measure gives me right result and loads the data of table visual in 30sec when i use this single measure along with required columns in a table visual.

The real problem is, table which i am referring here Sales Orders Snapshots has 50Millions of records.

And the same logic is been implemented for another 10 columns to see the before and new values with in a date range for each salesorder id and product id.

when i keep all those 20 Before and New calcualted measures in table visual, it is throwing an error of out of memory.

I would request you to please guide me on how i can optimize or come up with a better approach for this.

Thanks,

Mohan V.

[lbendlin]

Use DAX Studio.  Examine the query plan  (check for excessive number of records) and then refactor the query accordingly.  Try to reduce cardinality as early as possible (but not earlier).