Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!!!
Solved! Go to Solution.
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
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
Try
NextProgramSameDay column =
VAR CurrentDate = Table[Timestamp]
RETURN
COUNTROWS (
FILTER (
CALCULATETABLE (
Table,
ALLEXCEPT ( Table, Table[Device ID], Table[Timestamp] )
),
Table[Timestamp] > CurrentDate
)
)
Unfortunately, this does not work but returns an empty column.
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] )
)
)
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.
Are you creating a calculated column or w measure?
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
26 | |
10 | |
10 | |
9 | |
6 |