Forum Discussion
Find most recent value based on two criteria
I have two datasets.
Dataset 1 (transactions) has the following columns:
- Month, client, transactions
Dataset 2 (consultants) has the following columns:
- Client, consultant, date of start
I want to add a calculated column to the first dataset which returns the consultant based on client and date match. ofcourse, the second dataset only has a date of change so the calculated column must somehow determine the most 'recent' consultant that has been working on the client.
How can I do this?
HI, Anonymous
Sorry for my careless and misunderstanding for the issue,
and you may try to this formula as below:
Consultant = MAXX ( TOPN ( 1, FILTER ( consultants, results[Client] = consultants[Client] && results[Date] >= consultants[Starting date] ), consultants[Starting date], DESC ), consultants[Consultant] )Regards,
Lin Li
8 Replies
- Greg_Deckler
Community Champion
Try something like:
Column = VAR __table = RELATEDTABLE('Table21') VAR __maxDate = MAXX(__table,[date]) RETURN MAXX(FILTER(__table,[date]=__maxDate),[consultant])Thanks a lot for your suggestion Greg. How do you suggest I relate these tables, as there are no many-to-one relationships possible. There are many dates as there are many clients.
I do get this formula working, but it only returns the consultant with the most recent date, and only for client results in that specific month. It does not respect the client to client match.- Greg_Deckler
So are the tables related or no? You should be able to do a many-to-many now with composite models. Are they related by Date? If you could share some sample data and a pic of your relationships could potentially find a better solution.
