get value from another table based on 2 conditions, matching ID b/w tables and max date from org

[alya1]

Hellow I have 2 tables as below:
table 1:

ID	Date	Unit
1	1/1/2023	1
1	1/2/2023	1
1	1/3/2023	2
2	5/6/2023	5
2	5/7/2023	4

table 2:

ID

1

2

How do I add a new column to table 2 for Unit based on max Date from table 1 and matching ID?
goal table 2: 

ID	Unit
1	2
2	4

Thank you!

[Anonymous]

Hi,@alya1 

Regarding the issue you raised, my solution is as follows:

1. Below are the measure I've created for your needs:

unit = 
var maxcatime=CALCULATE(MAX('Table1'[Date]),FILTER(ALLSELECTED('Table1'),'Table1'[id]=MAX('Table1'[id])))
RETURN CALCULATE(MAX('Table1'[Unit]),FILTER(ALLSELECTED('Table1'),'Table1'[Date]=maxcatime))

2.Here's my final result, which I hope meets your requirements.

Best Regards,

Leroy Lu

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

[alya1]

Hi Leroy, thank you for your reply, I think it's on the right track! But would you mind double checking the DAX you posted please? I did it on my own and got "4" for both IDs:

I also realized the Unit column is text with some being R7 or E1 instead of just 7 or 1. Would that change the DAX?

[Anonymous · Accepted solution]

Hi,@alya1

Thanks for letting us know your user experience. According to your case description, I do understand how frustrated you are now.

Regarding the issue you raised, my solution is as follows:

1.I noticed that you are using calculated columns, while the code I gave is for measure. The way I display it is as follows.

And there is a relationship between my table:

2.Of course, if you want to use calculation columns, here is my calculation column:

unit Column = 
var maxcatime=CALCULATE(MAX('Table1'[Date]),FILTER('Table1','Table1'[id]=EARLIER('Table'[id])))
RETURN CALCULATE(MAX('Table1'[Unit]),FILTER(ALLSELECTED('Table1'),'Table1'[Date]=maxcatime))

3.Here is the final result, hopefully to meet your needs:

Also, my pbix file is attached to this reply, you can extract it according to your needs.

If you need other help from my side, please let me know.

Best Regards,

Leroy Lu

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