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, logou...
  • Jihwan_Kim's avatar
    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 () )