DAX to get latest data from another table

[Anonymous]

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!

[amitchandak · Accepted solution]

@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] )

[PaulDBrown · Accepted solution]

@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 · Accepted solution]

@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):

[Anonymous]

Hi Paul,

Thanks for this. The column for Maturity is sorted as Last Value. Can the measure that you provided be used in the Maturity as well if I have random values (cannot be alpha-sorted)?

[PaulDBrown]

@Anonymous 

Sorry, I don't understand what you mean. Care to provide an example/depiction?

[Anonymous]

For the column of Maturity, I was able to get the last value since it was sorted as "Last". I was thinking if there is a measure to get the latest value of Maturity based on date as some of the values will not be aphabetical.

really appreciate your wor here!

[v-kelly-msft · Accepted solution]

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,
Kelly

Did I answer your question? Mark my post as a solution!

[PaulDBrown]

I posted a measure which will return the rows with the latest date by region. So yes, it will return the latest "Maturity". Set up a table visual as I explained above

[amitchandak · Accepted solution]

@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] )