Need help in optimizing DAX query

[Mohan128256]

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.

[Mohan128256]

@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]

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

[Greg_Deckler]

@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]

@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.

[AlexisOlson]

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]

@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]

@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.