Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Instead of nesting FILTER calls, Putting a direct filters on CALCULATE can significantly improve the performance of your measure.
Thanks & Regards,
Bhavesh
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?
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.
This is sample data. Only one table is used for this particular calculation.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |