Forum Discussion

spocx's avatar
spocx
Icon for Helper I rankHelper I
8 years ago
Solved

Finding first value with same ID in multiple rows based on date/time

Dear Power BI community   I need help to write a DAX formular that can identify the first value in multiple rows with the same ID based on date/time. I have a table as shown below and need to calcu...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi spocx,

     

    First Assign team =
VAR firstassigndate =
    CALCULATE (
        MIN ( 'table 1'[Assign date/time] ),
        ALLEXCEPT ( 'table 1', 'table 1'[Ticket ID] )
    )
RETURN
    CALCULATE (
        SELECTEDVALUE ( 'table 1'[Team assigned] ),
        FILTER (
            ALLEXCEPT ( 'table 1', 'table 1'[Ticket ID] ),
            'table 1'[Assign date/time] = firstassigndate
        )
    )

     

    Best regards,

    Yuliana Gu

Anonymous's avatar
Anonymous
Not applicable
8 years ago

spocx try this

 

First assignment time = CALCULATE(MIN(Table1[Assign date/time]),ALLEXCEPT(Table1, Table1[Ticket ID]))
assign team for first assignment= IF([First assignment time]=Table1[Assign date/time],Table1[Team Assigned],NULL)
First assign team = CALCULATE(MAX(assign team for first assignment),ALLEXCEPT(Table1, Table1[Ticket ID]))