Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Get Last state from distinct users by date

I have a sessions details in my dataset that look like this: Session Date  User Result 4 01-04-21  A OK 3 01-03-21  A FAIL 2 01-02-21  B FAIL 1 01-01-21  C RETRY ...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    5 years ago

    Hi, Anonymous 

    Try  calculated column

    Expected result =
VAR last_sessionn =
    CALCULATE (
        MIN ( 'Table'[Session] ),
        FILTER (
            'Table',
            'Table'[User] = EARLIER ( 'Table'[User] )
                && 'Table'[Session] > EARLIER ( 'Table'[Session] )
        )
    )
RETURN
    IF (
        ISBLANK ( last_sessionn ),
        'Table'[Result],
        CALCULATE ( MAX ( 'Table'[Result] ), 'Table', 'Table'[Session] = last_sessionn )
    )

     

    or measure :

    Expected result2 =
VAR last_session =
    CALCULATE (
        MIN ( 'Table'[Session] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[User] ),
            'Table'[Session] > MAX ( 'Table'[Session] )
        )
    )
RETURN
    IF (
        ISBLANK ( last_session ),
        MAX ( 'Table'[Result] ),
        CALCULATE (
            MAX ( 'Table'[Result] ),
            ALL ( 'Table' ),
            'Table'[Session] = last_session
        )
    )

    Please check my pbix file for more details.

     

    Best Regards,
    Community Support Team _ Eason

Tahreem24's avatar
Tahreem24
Icon for Super User rankSuper User
5 years ago

Anonymous try this:.

 

=CALCULATE (MAX(Table[Result]), FILTER(Table, Table[Date]=MAX(Table[Date])), ALLEXCEPT(Table, Table[User])) 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thanks for your reply, but it does not work since I'm getting the same value for the whole column for your suggestion, independently on the user