Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 ...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
7 years ago

Try something like:

 

Column = 
VAR __table = RELATEDTABLE('Table21')
VAR __maxDate = MAXX(__table,[date])
RETURN
MAXX(FILTER(__table,[date]=__maxDate),[consultant])
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      7 years ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi Greg,

         

        This is an example of the data (can't share the real thing). Should've mentioned I was trying to achieve this in Power Query. I don't know if the composite models are availabe there: