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:

SessionDate UserResult
401-04-21 AOK
301-03-21 AFAIL
201-02-21 BFAIL
101-01-21 CRETRY
001-01-21 BOK


I would like to have an additional column that each row contains the value of the last result for the last session for a distinct user, such as:

SessionDate UserResultLast Result
401-04-21 AOKOK
301-03-21 AFAILOK
201-02-21 BFAILFAIL
101-01-21 CRETRYRETRY
001-01-21 B OKFAIL



I have tried to find simple ways by creating new columns and measures, but since the value of user vary, I think it can be a bit more complicated. 

Is there a simple way to achieve this result?

Thanks in advance!

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

4 Replies

  • 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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    5 years ago

    Hi,

    This calculated column formula works

    =LOOKUPVALUE(Data[Result],Data[User],Data[User],Data[Date],CALCULATE(MAX(Data[Date]),FILTER(Data,Data[User]=EARLIER(Data[User]))))

    Hope this helps.

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    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