Stack or List operation in DAX

[Anonymous]

Hi,

I have a log table that captures system statuses by time.

I need to create a Snapshot view(Table) that contains  a column with date, order no and concatenated active statuses by removing the ones that are In Active(or closed). The requirement is to perfrom Stack like operation and remove the latest corresponding entry/ perform List like Operation and remove the earliest corresponding entry.

System Statuses can repeat and multiple entries can be active at a specific time

For example, for Order No 10001 Status Code on 

2021-01-02  : CRTD, INPL, PLND, WFMR

2021-01-03  : CRTD, INPL, PLND, WFMR

2021-01-04  : CRTD, INPL, PLND, WFMR

2021-01-05  : CRTD, INPL, PLND, ORTS, INPL  ----> WFMR  is removed as we encountered X for the same on 4th. INPL repeats

2021-01-06  : CRTD, INPL, PLND, ORTS, INPL, PLND, WFMR  ----> WFMR  is re-introduced on 5th

2021-01-07  : CRTD, INPL, PLND, ORTS, INPL, PLND, TECO, CLSD

I did a  cross join with Calendar Date (field) and managed to create a Snapshot with all statuses. However removing correpsonding X ones is posing a challenge.

Note: It is not about excluding all statuses with X entry before date. 

For example, if we have a status WFMR showing up thrice with one entry having X ... we need to show WFMR once in the final output.

Any help would be appreciated.

Order No	Date Time	System Status Code	InActive
1000	2021-01-02 10:45	CRTD
1000	2021-01-02 11:15	INPL
1000	2021-01-03 10:45	PLND
1000	2021-01-03 10:45	WFMR
1000	2021-01-05 12:45	PLND	X
1000	2021-01-07 11:00	WFMR	X
1000	2021-01-08 12:00	ORTS
1000	2021-01-09 10:45	SCHD
1000	2021-01-10 14:00	TECO
1000	2021-01-10 16:00	CLSD
10001	2021-01-01 8:00	CRTD
10001	2021-01-01 10:00	INPL
10001	2021-01-01 14:00	PLND
10001	2021-01-01 15:00	WFMR
10001	2021-01-04 16:00	WFMR	X
10001	2021-01-04 18:00	ORTS
10001	2021-01-04 20:00	INPL
10001	2021-01-05 22:00	PLND
10001	2021-01-05 0:00	WFMR
10001	2021-01-06 2:00	WFMR	X
10001	2021-01-06 14:00	TECO
10001	2021-01-06 16:00	CLSD

Thanks,

Mannu

[lbendlin]

So X means "negate the previous entry"?  

Your timestamps are not unique, so the sort order cannot be guaranteed. You need to add an index column.

What should order 1000 be on 2021-01-07 ?  CRTD,INPL,PLND,PLND ? 

What is the cutoff time for each date? beginning or end of day?

[Anonymous]

Yes... X means closure of previous entry with same status code.

True.. time stamps are not unique and multiple system status codes can have the same date time.  Its fine even if order cannot be guaranteed. It is fine as long as it is sorted by date time when using CONCATENATEX ..etc

Snapshot captures the active statuses at the end of previous day.

Output for 1000 as on 2021-01-07 would be CRTD,INPL,WFMR  (PLND is closed on 5th and hence shouldn't be considered).

As on 2021-01-08 it would be CRTD, INPL (as both PLND and WFMR are closed)