Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |