Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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?

1 ACCEPTED SOLUTION

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

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

View solution in original post

8 REPLIES 8
Greg_Deckler
Super User
Super User

Try something like:

 

Column = 
VAR __table = RELATEDTABLE('Table21')
VAR __maxDate = MAXX(__table,[date])
RETURN
MAXX(FILTER(__table,[date]=__maxDate),[consultant])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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.

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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:2018-11-22 16_15_59-Window.png

 

2018-11-22 16_20_10-Window.png

 

 

 

hi, @Anonymous

After my research, you could try this formula:

result = 
SUMX (
    TOPN (
        1,
        FILTER (
            SUMMARIZE (
                results,
                results[Client],
                results[Date],
                "_transaction", CALCULATE ( SUM ( results[Transactions] ) )
            ),
            results[Client] = consultants[Client]
                && results[Date] <= consultants[Starting date]
        ),
        results[Date], DESC
    ),
    [_transaction]
)

Result:

2.JPG

 

here is pbix, please try it.

https://www.dropbox.com/s/n8rpg99uxh4m7iq/Find%20most%20recent%20value%20based%20on%20two%20criteria.pbix?dl=0

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi and thanks for your suggestion, I really appreciate all the ideas and input. However I don't think that your solution works for what I'm trying to achieve. The result should be that the right consultant is returned for the right date and client. 

 

The table should be something like this:

 

 

Date Client Transactions Consultant (calculated column)

DateClientTransactionsConsultant (calculated column)
1-1-2019Client A2000Consultant 1
1-1-2019Client B1500Consultant 2
3-1-2019Client C4387Consultant 3
1-6-2019Client A2433Consultant 2

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi Lin Li, this was a great answer and helped me to do exactly what I wanted. Thanks a lot!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.