I am struggling to subtract date from previous record date based on Status column. Below an example of what I'm trying to get. From SQL there is a DateTime, status and point column. For each point I want to get the AlarmTime (add column). For some PBI files this works fine but when there are more records (between 200,000 - 300,000 rows) my memory usage is getting high and stays hig (100%). Normal I use the following DAX and replace some to fit my current table it gets the job well done by max 60,000 rows above the memory usage is high (installed 32GB)
I wonder why the DAX works in most cases but not in a model with more records, I don't know how to fix this tried a lot of different DAX etc. Perhaps for lager models the way I want to get it done is not the right one....
DateTime Start =
MAX ( 'NSA Bedrijf'[DateTime End] ),
'NSA Bedrijf'[DateTime End]
< EARLIER('NSA Bedrijf'[DateTime End] )
Example of the lager models with high memory usage:
Hi @amitchandak, tried your solution. behalf it's not the complete solution it's certain a lot of help. Customized your solution and now it's working at lighting speed. In one of the added columns I now get the first date and time of the alarming state of a certain point. From now on I can calculate the difference in time between the normal state and alarm state. Further I can count the difference in time when a point is still in a alarm state.
So thanks a lot for your solution, I really appreciate your help