Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Getting the latest status from another table based on ID Ref

Hi All ,

 

I am trying to create a custom column to get the latest value from a related table based the latest modified time and date. I have written this formula but an error resulted due to multiple values being returned. 

 
Lastest Status = LOOKUPVALUE(Table_A[Status],Table_A[ID_Ref],Table_B[ID]) 

 

How should i write the DAX to return only the latest status? Appreciate your help ! 

 

Many Thanks

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    Try using 

     

    Operator=
CALCULATE (
    FIRSTNONBLANK ( Table_A[Status], 1 ),
    FILTER ( ALL ( Table_A ), Table_A[ID] = Table_B[ID] )
)

     

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      6 years ago

      Anonymous , Try as new colum 

      maxx(filter(Table, Table_A[ID_Ref]=Table_B[ID]),Table_A[Status])

      or

      maxx(filter(Table, Table_A[ID_Ref]=Table_B[ID]),lastnonblankvalue(Table_A[Date],max(Table_A[Status])))

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi amitchandak 

         

        Many thanks for your swift response and help ! 

         

        I tried out your suggestion and the end result is equiavlent to what Anonymous  proposed. But somehow the return value is not the latest value that it should return.  

         

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Anonymous 

       

      Thanks. Let me go try out !

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous ,

       

      Somehow it work for all my sample records (total around 13) except for 1. Not sure what went wrong with but i will recheck my datasource.

       

      Thanks !  

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Anonymous ,

         

        Try cleaning and trimming the  Column in Power Query.

         

         

        Table A

         

         

         

        Table B

         

         

         

         

        Regards,
        Harsh Nathani

        Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Anonymous 

    What I understand based on your explanation is that you are trying to add a calculated column on the one side of a many to one relation where TableB is on one side and Table A is on many side.

     

    This is the reason why you are getting multiple values.

     

    In this case you should try creating a calculated column as below on the Table B which is on one(1) side of (1..*) relation.

     

    Latest Status =
     MAXX ( RELATEDTABLE(TableA) , TableA[Status])

    You may need to alter this expression depending on your model as you say that is based on latest date and time.
     
    Hope you find this hhelpful.
     
    Thanks.
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous 

       

      Thank you for your response. I tried your suggestion but the result is not returning correctly though. I guess maxx with related table will return the largest value relevant to the associated ID but somehow it may not work for my context. Anyway, many thanks for your help!