Forum Discussion

Martin74's avatar
Martin74
Helper I
4 years ago
Solved

Get Date from previous status

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 =
IF('NSA Bedrijf'[ActualValue]=0,
CALCULATE (
MAX ( 'NSA Bedrijf'[DateTime End] ),
FILTER (
ALLEXCEPT (
'NSA Bedrijf',
'NSA Bedrijf'[PointName]
),
'NSA Bedrijf'[DateTime End]
< EARLIER('NSA Bedrijf'[DateTime End] )
)
),BLANK())
 
Example of the lager models with high memory usage:
 

 

Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    Martin74 , Try like

     


    maxx(filter( 'NSA Bedrijf' , 'NSA Bedrijf'[PointName] = earlier('NSA Bedrijf'[PointName]) && 'NSA Bedrijf'[DateTime End]< EARLIER('NSA Bedrijf'[DateTime End] ) ),'NSA Bedrijf'[DateTime End])

     

    do it two steps

     

    Rank  =Rankx (filter( 'NSA Bedrijf' , 'NSA Bedrijf'[PointName] = earlier('NSA Bedrijf'[PointName]) ),'NSA Bedrijf'[DateTime End] ,,asc, dense)

     


    maxx(filter( 'NSA Bedrijf' , 'NSA Bedrijf'[PointName] = earlier('NSA Bedrijf'[PointName]) && 'NSA Bedrijf'[Rank  ]= EARLIER('NSA Bedrijf'[Rank ] )  -1 ),'NSA Bedrijf'[DateTime End])

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Martin74 , Try like

     


    maxx(filter( 'NSA Bedrijf' , 'NSA Bedrijf'[PointName] = earlier('NSA Bedrijf'[PointName]) && 'NSA Bedrijf'[DateTime End]< EARLIER('NSA Bedrijf'[DateTime End] ) ),'NSA Bedrijf'[DateTime End])

     

    do it two steps

     

    Rank  =Rankx (filter( 'NSA Bedrijf' , 'NSA Bedrijf'[PointName] = earlier('NSA Bedrijf'[PointName]) ),'NSA Bedrijf'[DateTime End] ,,asc, dense)

     


    maxx(filter( 'NSA Bedrijf' , 'NSA Bedrijf'[PointName] = earlier('NSA Bedrijf'[PointName]) && 'NSA Bedrijf'[Rank  ]= EARLIER('NSA Bedrijf'[Rank ] )  -1 ),'NSA Bedrijf'[DateTime End])

    • Martin74's avatar
      Martin74
      Helper I
      4 years ago

      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

       

      Kind regards,

       

      Martin