Forum Discussion
DAX to get latest data from another table
Hello Everyone,
I hope someone can help me with this. I have 2 tables, I need to get the latest value from the second table based on datetime
Table 1:
| ID | Region |
| 1 | AMS |
| 2 | APJ |
| 3 | EMEA |
| 4 | AMS |
| 5 | APJ |
Table 2 :
| ID | Maturity | Latest Update |
| 1 | sample1.1 | 2/1/2021 15:45 |
| 2 | sample2.1 | 2/1/2021 4:00 |
| 3 | sample3.1 | 2/1/2021 0:00 |
| 4 | sample4.1 | 2/1/2021 0:00 |
| 5 | sample5.1 | 2/1/2021 0:00 |
| 1 | sample1.2 | 2/3/2021 0:00 |
| 1 | sample1.3 | 2/10/2021 4:00 |
| 2 | sample2.2 | 2/10/2021 5:00 |
| 3 | sample3.2 | 2/5/2021 0:00 |
| 3 | sample3.3 | 2/11/2021 18:00 |
result :
| ID | Region | Maturity | Latest Update |
| 1 | AMS | sample1.3 | 2/10/2021 4:00 |
| 2 | APJ | sample2.2 | 2/10/2021 5:00 |
| 3 | EMEA | sample3.3 | 2/11/2021 18:00 |
| 4 | AMS | sample4.1 | 2/1/2021 0:00 |
| 5 | APJ | sample5.1 | 2/1/2021 0:00 |
Thank you!
Anonymous , New column in table 1
date = maxx(filter(Table2, Table1[ID] = Table2[ID] ),Table2[Latest Update])
new column =
var _max = maxx(filter(Table2, Table1[ID] = Table2[ID] ),Table2[Latest Update])
return
maxx(filter(Table2, Table1[ID] = Table2[ID] && Table2[Latest Update] = _max ),Table[Maturity] )Anonymous
Here is one way. First the model:
Now create the measure:
Latest Update (measure) = VAR MXLU = MAXX ( FILTER ( ALL ( Table2 ), Table2[ID] = SELECTEDVALUE ( 'dim Region'[ID] ) ), Table2[Latest Update] ) RETURN IF ( MAX ( Table2[Latest Update] ) = MXLU, MAX ( Table2[Latest Update] ) )Set up a table visual (using the region fields from the dim table):
Hi Anonymous ,
Create a measure as below:
_Maturity = CALCULATE(MAX('Table 2'[Maturity]),FILTER('Table 2','Table 2'[Latest Update]=[_last update]&&'Table 2'[ID]=MAX('Table 1'[ID])))And you will see:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
7 Replies
- amitchandak
Super User
Anonymous , New column in table 1
date = maxx(filter(Table2, Table1[ID] = Table2[ID] ),Table2[Latest Update])
new column =
var _max = maxx(filter(Table2, Table1[ID] = Table2[ID] ),Table2[Latest Update])
return
maxx(filter(Table2, Table1[ID] = Table2[ID] && Table2[Latest Update] = _max ),Table[Maturity] ) 
PaulDBrownCommunity Champion
Anonymous
Here is one way. First the model:
Now create the measure:
Latest Update (measure) = VAR MXLU = MAXX ( FILTER ( ALL ( Table2 ), Table2[ID] = SELECTEDVALUE ( 'dim Region'[ID] ) ), Table2[Latest Update] ) RETURN IF ( MAX ( Table2[Latest Update] ) = MXLU, MAX ( Table2[Latest Update] ) )Set up a table visual (using the region fields from the dim table):
- PaulDBrown
Anonymous
Sorry, I don't understand what you mean. Care to provide an example/depiction?