Forum Discussion

clteh's avatar
clteh
Frequent Visitor
3 years ago
Solved

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, logout_dt = next logon_dt. 

 

existing table:

user logon_dtlogout_dt
userA2022-11-22 08:10:00 
userB2022-11-22 08:20:00 
userC2022-11-22 08:30:00 
userA 2022-11-22 12:10:00
userA2022-11-22 13:00:00 
userB2022-11-22 13:20:00 

 

New table

user logon_dtlogout_dtduration [hh:mm]
userA2022-11-22 08:10:002022-11-22 12:10:0004:00
userB2022-11-22 08:20:002022-11-22 13:20:0005:00
userC2022-11-22 08:30:00  
userA2022-11-22 13:00:00  
userB2022-11-22 13:20:00  

 

thanks 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    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 () )

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    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 () )
    • clteh's avatar
      clteh
      Frequent Visitor
      3 years ago

      thanks. it is working.