Forum Discussion

Mohan128256's avatar
Mohan128256
Helper IV
2 years ago

Need help in optimizing DAX query

Hello All,

 

I have written below dax which is taking 30+ sec of time to execute and produce the results.

 

_InventoryAmtConversed = 
VAR _Onhand_StdCost = 
CALCULATE(SUM('Inventory Snapshots'[On-hand StdCost$]),KEEPFILTERS('Inventory Snapshots'[Snapshot Weekly?] = "Y"), KEEPFILTERS('Calendar'[445 Year Week Offset]=0))
VAR _PickedQty = 
CALCULATE(SUM('Inventory Snapshots'[Picked Qty]),KEEPFILTERS('Inventory Snapshots'[Snapshot Weekly?] = "Y"), KEEPFILTERS( 'Calendar'[445 Year Week Offset]=0))
VAR _currency = MAX( 'Inventory Snapshots'[Currency]) --USD
 
VAR _currencySelected = SELECTEDVALUE('Exchange Rates'[To Currency],"USD")

VAR _ConversationRate = 
         SWITCH(_currency ,_currencySelected, 1,
             
                 MAXX(KEEPFILTERS(FILTER('Exchange Rates','Exchange Rates'[From Currency] = _currency)),'Exchange Rates'[Exchange Rate])
             )
RETURN

(_Onhand_StdCost+_PickedQty)*_ConversationRate

 

 

Background of this measure is...
We have a Rates table which is standalone table where we choose Currency type from slicer and based on it i am trying to calculate conversion rate

 

VAR _currency = MAX( 'Inventory Snapshots'[Currency]) --only USD currency type in fact table 
VAR _currencySelected = SELECTEDVALUE('Exchange Rates'[To Currency],"USD") -- Slicer Selection
_ConversationRate = 
         SWITCH(_currency ,_currencySelected, 1,
             
                 MAXX(KEEPFILTERS(FILTER('Exchange Rates','Exchange Rates'[From Currency] = _currency)),'Exchange Rates'[Exchange Rate])
             )

 

 

Then, the fact table 'Inventory Snapshots' has two columns,

On-hand StdCost$ & Picked Qty.
But there are filters to apply on these to get the total value
  • 'Calendar'[445 Year Week Offset] = 0
  • 'Inventory Snapshots'[Snapshot Weekly?] = "Y"

Calender table having 1 to many relaitonship with Inventory Snapshots.

 

As i said the query was taking almost 30+ sec of time to execute. Sometimes it throws an error as out of memory.

Can anyone please help me make it perform better than what it is now.

Greg_Deckler 

 

Thanks,

Mohan V.

 

Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

7 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    2 years ago

    This looks fairly clean already. That said, you could try replacing your MAXX with a LOOKUPVALUE. That is,

    VAR _ConversationRate =
    SWITCH (
        _currency,
        _currencySelected, 1,
        LOOKUPVALUE (
            'Exchange Rates'[Exchange Rate],
            'Exchange Rates'[From Currency], _currency
        )
    )

     

    Can you pinpoint what part is slow? Try replacing _Onhand_StdCost, _PickedQty, and _ConversationRate with a constant (e.g. 2) one at a time to see which one(s) are slowing it down.

     

    • Mohan128256's avatar
      Mohan128256
      Helper IV
      2 years ago

      AlexisOlson  thanks for the reply. Appreciate it.

       

      I tried this earlier..but it made more complicated than earlier.

      I will try to dig it out by your suggestion.

       

      Thanks,

      Mohan V.

      • Mohan128256's avatar
        Mohan128256
        Helper IV
        2 years ago

        AlexisOlson Greg_Deckler 

         

        When i run the both Onhand StdCost+PickedQty it took hardly 2 sec.

        But then when i added the multiplication with conversionrate it is taking more time like almost 30+ sec.

        I believe it is because of the switch case that i am using in here.

        Not sure how can i make it quick.

        Any suggestions please.

         

        Thanks

        Mohan V.

         

         

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    Mohan128256 Would love to have the PBIX for this for testing. However, you might try something like this as you are calculating the same table twice in your code:

    VAR _Table = CALCULATETABLE( 'Inventory Snapshots', KEEPFILTERS('Inventory Snapshots'[Snapshot Weekly?] = "Y"), KEEPFILTERS('Calendar'[445 Year Week Offset]=0))
VAR _Onhand_StdCost = SUMX( _Table, [On-hand StdCost$])
VAR _PickedQty = SUMX( _Table, [Picked Qty])
    • Mohan128256's avatar
      Mohan128256
      Helper IV
      2 years ago

      Greg_Deckler  thanks for your reply.


      I have sent you the personal message with the report google drive link.

       

      Will be waiting for your reply with thousand open eyes. 😃

       

      Thanks,

      Mohan V.

  • Mohan128256's avatar
    Mohan128256
    Helper IV
    2 years ago

    Greg_Deckler AlexisOlson 
    I think i did made it more effeciant with what you guys have suggested.

     

    Now the query is running in less than a sec.

     

    here is it

    VAR _Table = CALCULATETABLE( 'Inventory Snapshots', KEEPFILTERS('Inventory Snapshots'[Snapshot Weekly?] = "Y"), KEEPFILTERS('Calendar'[445 Year Week Offset]=0))
VAR _Onhand_StdCost = SUMX( _Table, [On-hand StdCost$])
VAR _PickedQty = SUMX( _Table, [Picked Qty])
VAR _currency = MAX( 'Inventory Snapshots'[Currency]) --USD
 
VAR _currencySelected = 
MAXX(ALLSELECTED('Exchange Rates'[To Currency]), 'Exchange Rates'[To Currency])

VAR _ConversationRate = 
        LOOKUPVALUE (
            'Exchange Rates'[Exchange Rate],
            'Exchange Rates'[To Currency], _currencySelected
            ,'Exchange Rates'[From Currency],"USD"
        )*1
RETURN

IF(_currencySelected = "USD",(_Onhand_StdCost+_PickedQty),
(_Onhand_StdCost+_PickedQty*_ConversationRate))

     

    As i said the culprit was switch...but before that i see it was having major concern at selectedvalue of to currency.

    So i did change it to 

    VAR _currencySelected = 
MAXX(ALLSELECTED('Exchange Rates'[To Currency]), 'Exchange Rates'[To Currency])

     

    And We have USD value also in To Currency which should get multiplied by 1, So i tried to avoid that condition by making it into a final switch case with less condition.

    VAR _ConversationRate = 
        LOOKUPVALUE (
            'Exchange Rates'[Exchange Rate],
            'Exchange Rates'[To Currency], _currencySelected
            ,'Exchange Rates'[From Currency],"USD"
        )*1
RETURN

IF(_currencySelected = "USD",(_Onhand_StdCost+_PickedQty),
(_Onhand_StdCost+_PickedQty*_ConversationRate))

     


    Greg_Deckler  You are my guardian angel whenever i get struck with these sort of situations. You always comes to rescue me. Keep doing what you are doing. ❤️

     

    Thanks.

    Mohan V.

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      2 years ago

      Mohan128256 Happy to help!. Wow, that's a pretty dramatic speed improvement from 30 seconds to under a second!