Forum Discussion
data not refreshing
hallo Everyone,
I have big data. Two tables each with more than 1,500,000 rows and will keep expanding.
Everything was working well untill I added a calculated column in on of the tables (below)
after adding this calculated column the data is refreshing then stopping at one point and no matter how I wait it does not go futher to update.
Is there a way to solve this issue.
new column =
var _max =maxx(filter(Table, [ID] = earlier([ID]) && [datetime] <earlier([Datetime])),[Datetime])
return
if(isblank(_max), [value] , [value] -maxx(filter(Table, [ID] = earlier([ID]) && [datetime] =_max),[value]))
5 Replies
Anonymous , In file-> Options and Settings -> Options -> Data Load, Increase cache size.
Also increase virtual RAM of the system if needed
Please try this expression instead. It would also be better to split your datetime column to date and time columns (or just convert to date if you don't need your time component); that will help the performance of your model overall.
NewColumn =
VAR vThisID = Table[ID]
VAR vThisDateTime = Table[Datetime]
VAR vPrevDatetime =
CALCULATE (
MAX ( table[datetime] ),
Table[ID] = vThisID,
Table[Datetime] < vThisDateTime
)
VAR result =
CALCULATE (
MAX ( table[Value] ),
Table[ID] = vThisID,
Table[Datetime] = vPrevDatetime
)
RETURN
IF ( ISBLANK ( vPrevDateTime ), Table[Value], result )Pat
Hi Anonymous
This may be caused by your calculated column formula.
Use VAR for calculations as much as possible and avoid using Filter as a filter arguement, which can greatly reduce the memory required by your query and improve the performance of your query.
For reference: DAX: Use variables to improve your formulas
DAX: Avoid using FILTER as a filter argument
To optmizae your Power BI performance, you may refer to this blog.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.
Best Regards,
Rico Zhou
