Getting the latest status from another table based on ID Ref

[Anonymous]

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

[Anonymous]

@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]

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!  

[harshnathani]

Hi @Anonymous ,

You can also see

https://community.powerbi.com/t5/Desktop/Lookup-value-error-multiple-values-was-supplied/td-p/768049

https://community.powerbi.com/t5/Desktop/LOOKUPVALUE-with-multiple-results/td-p/541324

https://www.youtube.com/watch?v=e8OWQ7Xl3m0

Regards,

Harsh Nathani

[Anonymous]

Hi @harshnathani ,

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 !  

[harshnathani]

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]

@harshnathani 

Thanks. Let me go try out !

[harshnathani]

Hi @Anonymous ,

Try using 

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

[amitchandak]

@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]

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.  

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format?