How to find the first event attended by each employee and display it in another column by using DAX

employee_id   event        date     Expected outcome
100         event abc    2/16/2021    event def
100         event def    3/3/2019     event def
100         event ghi    4/13/2021    event def
101         event qwe    5/25/2022    event asd
101         event rty    12/31/2021   event asd
101         event asd    6/6/2019     event asd
101         event zxc    7/8/2021     event asd

Hi everyone,

I want to find the first event attended by each employee and display it in another column by using DAX. The 4th column in the example table above is the expected outcome that I want.

The DAX below is what I have tried, but I only can get the date for the first event that the employee attended instead of the event name itself. I tried to use LOOKUPVALUE but seems like it doesn't work:

first_event = 

VAR employeeId = [employee_id]
VAR expectedoutcome = MINX(FILTER(ALL(Table), Table[employee_id]=employeeId),[date])

Return expectedoutcome

Any help or advise will be greatly appreciated!

Solution

try this calculated column...

first event attended =
VAR _firstdate =
    CALCULATE ( MIN ( 'Table'[date] ), ALLEXCEPT ( 'Table', 'Table'[employee_id] ) )
RETURN
    CALCULATE (
        FIRSTNONBLANK ( 'Table'[event], 1 ),
        _firstdate = 'Table'[date],
        ALLEXCEPT ( 'Table', 'Table'[employee_id] )
    )