data not refreshing

[TatyDo]

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

[v-rzhou-msft]

Hi @TatyDo 

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

[v-rzhou-msft]

Hi @TatyDo 

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. 

[mahoneypat]

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

[amitchandak]

@TatyDo , In file-> Options and Settings -> Options -> Data Load, Increase cache size.

Also increase virtual RAM of the system if needed

[TatyDo]

Thank you for the answer, I have increased the RAM and the cashe size but it did not work, and when I look at the information of memory and CPU it seems to be on the average of 50% and there is no pressure there. Do you have other ideas of where it might be the problem?