Forum Discussion

ALRUYOYO's avatar
ALRUYOYO
Icon for Advocate I rankAdvocate I
9 years ago

Filter limitations working with large tables

Hi,

 

I have a table of completed orders and I want to calculate orders to date (OTD) for each visitor.
I have written this formula:

OTD =
VAR VID = 'powerbi CompletedOrders'[VisitorId]
VAR OID = 'powerbi CompletedOrders'[OrderId]
RETURN
CALCULATE (
COUNT('powerbi CompletedOrders'[OrderId]),
FILTER (
'powerbi CompletedOrders',
'powerbi CompletedOrders'[VisitorId]=VID),
FILTER (
'powerbi CompletedOrders',
'powerbi CompletedOrders'[OrderId]<OID))

I get this error: "The operation has been cancelled because there is not enough memory available for the application."

I understand that my table is too big (200k+ rows) for double Filter. I was looking how to write something similar to LOOP function in DAX but with no success.

I would love to hear your suggestions for a workaround.

Thanks!

4 Replies

  • BhaveshPatel's avatar
    BhaveshPatel
    Icon for Super User rankSuper User
    9 years ago

    Instead of nesting FILTER calls, Putting a direct filters on CALCULATE can significantly improve the performance of your measure. 

     

    Thanks & Regards,

    Bhavesh

     

     

     

    • ALRUYOYO's avatar
      ALRUYOYO
      Icon for Advocate I rankAdvocate I
      9 years ago

      Thank you for your answer BhaveshPatel. But could I am not sure I understand how to do it.
      Could you edit my function to illustrate your suggestion?

      • BhaveshPatel's avatar
        BhaveshPatel
        Icon for Super User rankSuper User
        9 years ago

        Can you please share the screenshots of your data model and sample data if possible. This will help me to provide you an exact solution.