topic Re: Getting the latest status from another table based on ID Ref in DAX Commands and Tips

Getting the latest status from another table based on ID Ref

Anonymous — Wed, 29 Jul 2020 04:45:49 GMT

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

Re: Getting the latest status from another table based on ID Ref

Anonymous — Wed, 29 Jul 2020 04:53:36 GMT

Hi Anonymous ,

Try using

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

Re: Getting the latest status from another table based on ID Ref

Anonymous — Wed, 29 Jul 2020 04:56:21 GMT

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

Re: Getting the latest status from another table based on ID Ref

Anonymous — Wed, 29 Jul 2020 05:09:19 GMT

Anonymous

Thanks. Let me go try out !

Re: Getting the latest status from another table based on ID Ref

amitchandak — Wed, 29 Jul 2020 05:38:16 GMT

Anonymous , Try as new colum

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

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

Re: Getting the latest status from another table based on ID Ref

Anonymous — Wed, 29 Jul 2020 06:11:28 GMT

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.

Re: Getting the latest status from another table based on ID Ref

Anonymous — Wed, 29 Jul 2020 06:21:15 GMT

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 !

Re: Getting the latest status from another table based on ID Ref

Anonymous — Wed, 29 Jul 2020 07:17:29 GMT

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!

Re: Getting the latest status from another table based on ID Ref

Anonymous — Wed, 29 Jul 2020 07:24:05 GMT

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.

Re: Getting the latest status from another table based on ID Ref

amitchandak — Wed, 29 Jul 2020 07:25:22 GMT

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

Re: Getting the latest status from another table based on ID Ref

Anonymous — Wed, 29 Jul 2020 10:37:39 GMT

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)