how get last record of relation

[froxas]

Hi,

I have two tables Invoices and Clients. Relation is Client_id with Id. Many-to-many.

I need create a column in table Invoices and lookup client name from Clients table.

But the problem is that in a Client table contains one client with many versions. and by doing lookup into Invoices table I need to take last client version.

How to atchieve that?

[v-xicai · Accepted solution]

Hi @froxas ,

You can create column like DAX below.

Client_name = MAXX(TOPN(1,FILTER(Clients,Clients[Id]=INVOICES[Client_ID]),Clients[Version],DESC),Clients[Client_name])

Best Regards,

Amy

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

You can do if by first populating the version and then bringing the name. Try this as column

Max Version = maxx(filter(client,client[client_id] =sales[client_id]),client[version])

Client Name = maxx(filter(client,client[client_id] =sales[client_id] && client[version] =sales[Max Version]),client[Name])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[kentyler]

It sounds like you have what is called a "Slowly Changing Dimension". Clients probably have many records in the client table because when their record changes a new version is created, rather than just overwriting the original, but they keep the same "Client ID" in the invoices table.

One way people handle this is to have a flag on the client table marking the "current client" and then they always link to that record. This is the same as your wanting to link to the "lastest version" of the client. The problem with this approach can be that if you go back 3 months or 6 months, the client may have changed "after" the invoice was created. So if you use the "latest" version of the client, you will not have the correct version to match to the invoice.

If you are not interested in any client information that may have changed in the past, then you could just discard all the rows from the client table except the "latest" rows. You could do this before you load the data into Power BI, or in Power Query after it is loaded.
You could also create a calculated column in the client table that was true if the record was the "latest" record for that client, and then filter for only clients where that field was true.
Another that is often used is to have what is called a "surrogate key". You have a key for each version of the client, and that key is also added to the invoice. Then you know which version of the client was active when the invoice was created.
This is an issue with many options and many results, depending on the option you pick. If you would like to explore it further you can email me and I'll set up a screen share and we can discuss it.

My email is ken@8thfold.com, let me know what would be a good day and time to talk.