Forum Discussion
Advocate IGetting the next status to current column
Hello,
I need to check next status(st)=open for an id and then update the current row with a flag=A. I tried with earlier function but unable to get the expected result. Please suggest how to implement using DAX.
id,st,cdate,flag
1,st,jan17,A
1,open,feb17,NULL
Thank you
In this scenario, you can create a measure for next date first.
NextDate = CALCULATE ( MIN ( 'Table'[Date] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Id] ), 'Table'[Date] > MAX ( 'Table'[Date] ) ) )Then create a calculated column to lookup the corresponding Status based on NextDate.
NextStatus = LOOKUPVALUE ( 'Table'[Status], 'Table'[Date], [NextDate], 'Table'[Id], 'Table'[Id] )Regards,
5 Replies
- prateekraina
Memorable Member
Hi aj1107,
What happens to the previous row? Should the flag be changed to NULL from A in that?
Prateek Raina
aj1107Hi
I've added few more records. whenever next st is open for the ID set then update current row as A. if the next st is <>open or no more records for the id Set (last row of each id) then NULL.
id,st,cdate
1,close,jan17,A
1,open,feb17,NULL
1,close,mar17,A
1,open,apr17,NULL
2,st,jan17,NULL
3,st,jan17,A
3,open,Feb17,NULL
- prateekraina
Hi aj1107,
How about below DAX:
Flag = IF(Table1[St] = "Open","NULL","A")
Gives below result:
Does it work for you?
Prateek Raina
