Faster measure to sum the total inventory for the minimum chosen date from a slicer

[RettT]

I'm just trying to have the user be able to select two dates with a slicer and show the sum of the inventory on the starting date and another measure to show the ending date. This can be accomplished by the below formulas, but the table is 90 million rows and takes 90 seconds to calculate. Is there a faster way to do this? 

Starting inv date = CALCULATE([OH (Avg Cost each Day) by Day SUM],FILTER('Daily Inventory','Daily Inventory'[Date]=MIN('Daily Inventory'[Date])))

Ending inv date = CALCULATE([OH (Avg Cost each Day) by Day SUM],FILTER('Daily Inventory','Daily Inventory'[Date]=Max('Daily Inventory'[Date])))

[Ashish_Mathur]

Hi,

Does this work any faster?

1. Create a Calendar Table
2. Create a relationship (Many to One and Single) from the Date column of the Fact table to the Date column of the Calendar Table
3. To your slicer, drag Date from the Calendar Table
4. Write these measures

Starting inv date = CALCULATE([OH (Avg Cost each Day) by Day SUM],datesbetween(calendar[date],min(calendar[date]),min(calendar[date])))

Ending inv date = CALCULATE([OH (Avg Cost each Day) by Day SUM],datesbetween(calendar[date],max(calendar[date]),max(calendar[date])))

[RettT]

It's just 

OH (Avg Cost each Day) by Day SUM =

SUMX(

    'Daily Inventory',

    'Daily Inventory'[Qty OH] * 'Daily Inventory'[Average Cost (TS)]

)

[Greg_Deckler · Accepted solution]

@RettT You can try this:

Starting inv date = 
  VAR __Date = MIN( 'Daily Inventory'[Date] )
  VAR __Table = FILTER( 'Daily Inventory', 'Daily Inventory'[Date] = __Date )
  VAR __Result = SUMX( __Table, [Qty OH] * [Average Cost (TS)] )
RETURN
  __Result

Ending inv date = 
  VAR __Date = MAX( 'Daily Inventory'[Date] )
  VAR __Table = FILTER( 'Daily Inventory', 'Daily Inventory'[Date] = __Date )
  VAR __Result = SUMX( __Table, [Qty OH] * [Average Cost (TS)] )
RETURN
  __Result

I've seen CALCULATE get jammed up with single table data models and create inefficient query plans. I have also seen where having all of the code in the same measure speeds things up. No guarantees though.

[RettT]

That was WAY faster...down to like 5 seconds. Thank you!

[Greg_Deckler]

@RettT No CALCULATE wins again!!

[Martin_D]

@Greg_Deckler @RettT 
CALCULATE would also perform better with a column filter applied rather than a tabel filter applied:

Starting inv date = 
VAR _Date = MIN ( 'Daily Inventory'[Date] )
CALCULATE (
    [OH (Avg Cost each Day) by Day SUM],
    'Daily Inventory'[Date] = _Date
)

Ending inv date = 
VAR _Date = MAX ( 'Daily Inventory'[Date] )
RETURN
CALCULATE (
    [OH (Avg Cost each Day) by Day SUM],
    'Daily Inventory'[Date] = _Date
)

I tried all three approaches on a 12 million rows table, and CALCULATE with a column filter was fastest. So, no CALCULATE wins the hearts, CALCULATE wins the performance.

I'd be curious to hear how this performs on the original data model.

[Greg_Deckler]

@Martin_D I think the key here is the original data model because I have a 500 million row semantic model and all three versions have DAX query speeds of like 4 milliseconds. So ?

[Martin_D]

@Greg_Deckler That's why I'd be curious to hear how this performs on the original data model.

[Greg_Deckler]

@RettT What is the code for your [OH (Avg Cost each Day) by Day SUM] measure as that is likely the root of the problem.