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 get the above message when refreshing on Power BI Online.
I have isolated the problem and know that it is literally related to the one calculated column below:
_Next Order Date = CALCULATE(MIN(Order_Header[Date]),FILTER(Order_Header,Order_Header[Date] > EARLIER ( Order_Header[Date] )
&& Order_Header[Customer_Email] = EARLIER ( Order_Header[Customer_Email] )))
I need this for a host of different measures. It just gives the next order date for a particular customer.
Is there a way to change the setting on power bi online to accomodate this before I look at ways of changing the DAX?
Many thanks in advance
First off, one should refrain from calculating columns via DAX (too many reasons to state). It should be done in the source or in Power Query, where such calculations truly belong and where they'll be fast. Second, one should never use CALCULATE in calculated columns on big fact tables. CALCULATE executes context transition and this is always very costly. If you pair it with the number of rows that this needs to be calculated for in a fact table... well, you get the gist. It'll almost always throw a memory-overflow error.
Here's a version that does not use CALCULATE, so there's a chance it'll work. But, as I said, you should ideally calculate this outside DAX for speed and memory efficiency reasons.
[_Next Order Date] =
var CurrentDate = Order_Header[Date]
var CurrentEmail = Order_Header[Customer_Email]
var Result =
MINX(
FILTER(
Order_Header,
Order_Header[Date] > CurrentDate
&&
Order_Header[Customer_Email] = CurrentEmail
),
Order_Header[Date]
)
return
Result
@analytics_uk , what is the source data? How many rows
I am assuming you are getting this on power bi service, not desktop.
If Desktop, check
https://windowsreport.com/memory-error-allocation-failure/
Hi @amitchandak
there are some 200k rows on the table where that calculated column lies.
It comes from a SQL query that i wrote, if that's what you mean by source data?
And yes, I get the error on power bi service and not desktop
@analytics_uk , Please also log an issue - https://community.powerbi.com/t5/Issues/idb-p/Issues
this does seem like a big number.
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 |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |