Forum Discussion

RettT's avatar
RettT
Regular Visitor
1 year ago
Solved

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

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...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    1 year ago

    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.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
1 year ago

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's avatar
RettT
Regular Visitor
1 year ago

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

 

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    1 year ago

    RettT No CALCULATE wins again!!

    • Martin_D's avatar
      Martin_D
      Solution Sage
      1 year ago

      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's avatar
        Greg_Deckler
        Community Champion
        1 year ago

        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 ?