Forum Discussion
clteh
3 years agoFrequent Visitor
Create new table from existing table
hi, i would like to create a new logon/logoff table from existing table. new table will find the matching logout_dt for a particular user. If there is no logout prior to the next logon, logou...
- 3 years ago
Hi,
Please check the below picture and the attached pbix file.
New table = VAR _logoutdatecolumn = ADDCOLUMNS ( Data, "@logout_dt", VAR _nextlogondt = MINX ( FILTER ( Data, Data[user] = EARLIER ( Data[user] ) && Data[logon_dt] > EARLIER ( Data[logon_dt] ) ), Data[logon_dt] ) VAR _nextlogoutdt = MINX ( FILTER ( Data, Data[user] = EARLIER ( Data[user] ) && OR ( Data[logon_dt] > EARLIER ( Data[logon_dt] ), ISBLANK ( Data[logon_dt] ) ) && Data[logout_dt] < _nextlogondt ), Data[logout_dt] ) RETURN IF ( _nextlogoutdt <> BLANK (), _nextlogoutdt, _nextlogondt ) ) VAR _durationhrcolumn = ADDCOLUMNS ( _logoutdatecolumn, "@durationmin", DATEDIFF ( Data[logon_dt], [@logout_dt], MINUTE ) ) RETURN FILTER ( _durationhrcolumn, Data[logon_dt] <> BLANK () )
Jihwan_Kim
3 years agoSuper User
Hi,
Please check the below picture and the attached pbix file.
New table =
VAR _logoutdatecolumn =
ADDCOLUMNS (
Data,
"@logout_dt",
VAR _nextlogondt =
MINX (
FILTER (
Data,
Data[user] = EARLIER ( Data[user] )
&& Data[logon_dt] > EARLIER ( Data[logon_dt] )
),
Data[logon_dt]
)
VAR _nextlogoutdt =
MINX (
FILTER (
Data,
Data[user] = EARLIER ( Data[user] )
&& OR ( Data[logon_dt] > EARLIER ( Data[logon_dt] ), ISBLANK ( Data[logon_dt] ) )
&& Data[logout_dt] < _nextlogondt
),
Data[logout_dt]
)
RETURN
IF ( _nextlogoutdt <> BLANK (), _nextlogoutdt, _nextlogondt )
)
VAR _durationhrcolumn =
ADDCOLUMNS (
_logoutdatecolumn,
"@durationmin", DATEDIFF ( Data[logon_dt], [@logout_dt], MINUTE )
)
RETURN
FILTER ( _durationhrcolumn, Data[logon_dt] <> BLANK () )- clteh3 years agoFrequent Visitor
thanks. it is working.