topic Re: New column with value of next row grouped/filtered by multiple columns in DAX Commands and Tips

New column with value of next row grouped/filtered by multiple columns

Opilio2 — Mon, 25 Apr 2022 14:23:54 GMT

Hi everyone,

since I'm no DAX pro, I urgently need your help with the following issue.

Background: I'm analyzing the use of electronic devices. To be specific, I want to know how often users start different sequential programs in the daily use. That is, if a certain program on device A on day x is started, what is the next program?

Data basically looks like this:

Device ID	Timestamp	Program ID
A	2022-01-01 14:38	ID1
B	2022-01-01 09:12	ID 2
A	2022-01-03 19:45	ID 3
C	2022-01-04 19:45	ID 2
B	2022-01-01 11:03	ID 2
B	2022-01-01 22:54	ID 1
A	2022-01-03 23:09	ID 1

I need a new column "NextProgramSameDay" which, for every distinct device and every single day, contains the program ID of the directly following program on the same day:

Device ID	Timestamp	Program	NextProgramSameDay	Comment (for clarification, not to be included in the dataframe)
A	2022-01-01 14:38	ID 1		no next program because no additional program was started on the same day in device A
B	2022-01-01 09:12	ID 2	ID 2	Next program on device B on same day is program 2 at 11:03
A	2022-01-03 19:45	ID 3	ID 1	Next program on device A on same day is program 1 at 23:09
C	2022-01-04 19:45	ID 2		no next program because no additional program was started on the same day in device C
B	2022-01-01 11:03	ID 2	ID 1	Next program on same day in device B is program 1 at 22:54
B	2022-01-01 22:54	ID 1		no next program because no additional program was started on the same day in device B
A	2022-01-03 23:09	ID 1		no next program because no additional program was started on the same day in device A

Can anyone tell me how to accomplish this?

Thanks a lot in advance!!!

Re: New column with value of next row grouped/filtered by multiple columns

tamerj1 — Mon, 25 Apr 2022 13:18:08 GMT

Hi Opilio2

if you are looking for a column you can use

NextProgramSameDay column = COUNTROWS ( FILTER ( CALCULATETABLE ( Table, ALLEXCEPT ( Table, Table[Device ID], Table[Timestamp] ) ), Table[Timestamp] > EARLIER ( Table[Timestamp] ) ) )

Re: New column with value of next row grouped/filtered by multiple columns

Opilio2 — Mon, 25 Apr 2022 14:28:08 GMT

Hey tamerj1 ,
many thanks for your fast reply - this doesn't seem to be the solution, though; your suggestion only delivers blanks.
Additionally, I might have been mistakable, though- I'm looking for the following program ID/ name, given a certain deive ID and the date.

Re: New column with value of next row grouped/filtered by multiple columns

tamerj1 — Mon, 25 Apr 2022 14:34:16 GMT

Are you creating a calculated column or w measure?

Re: New column with value of next row grouped/filtered by multiple columns

tamerj1 — Mon, 25 Apr 2022 14:38:06 GMT

Opilio2

Try

NextProgramSameDay column = VAR CurrentDate = Table[Timestamp] RETURN COUNTROWS ( FILTER ( CALCULATETABLE ( Table, ALLEXCEPT ( Table, Table[Device ID], Table[Timestamp] ) ), Table[Timestamp] > CurrentDate ) )

Re: New column with value of next row grouped/filtered by multiple columns

Opilio2 — Tue, 26 Apr 2022 07:00:22 GMT

Unfortunately, this does not work but returns an empty column.

Re: New column with value of next row grouped/filtered by multiple columns

tamerj1 — Tue, 26 Apr 2022 08:34:40 GMT

Hi Opilio2
Yes you are right. I missed that we are dealing with time rather than date. Please use

NextProgramSameDay column = VAR CurrentTimeStamp = 'Table'[Timestamp] VAR CurrentDate = DATE ( YEAR ( CurrentTimeStamp ), MONTH ( CurrentTimeStamp ), DAY ( CurrentTimeStamp ) ) VAR FilteredDeviceTable = FILTER ( CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Device ID] ) ), DATE ( YEAR ( 'Table'[Timestamp] ), MONTH ( 'Table'[Timestamp] ), DAY ( 'Table'[Timestamp] ) ) = CurrentDate && 'Table'[Timestamp] > CurrentTimeStamp ) VAR NextTimeStamp = MINX ( FilteredDeviceTable, 'Table'[Timestamp] ) VAR NextProgram = MINX ( FILTER ( FilteredDeviceTable, 'Table'[Timestamp] = NextTimeStamp ), 'Table'[Program ID] ) RETURN NextProgram