Newest value for each ID from other table

[mober]

I have two tables:

• Table 1: Static data for each ID 
• Table 2: Transactional data

I want to look up the newest value in Table 2 and add it as a new column in Table 1.

See below tables for the desired output:

[Icey · Accepted solution]

Hi @mober ,

Try to create a calculated column like so:

Latest Location =
VAR LatestDate =
    CALCULATE (
        MAX ( 'Table 2'[Date] ),
        FILTER ( 'Table 2', 'Table 2'[ID] = EARLIER ( 'Table 1'[ID] ) )
    )
RETURN
    CALCULATE (
        MAX ( 'Table 2'[Location] ),
        FILTER (
            'Table 2',
            'Table 2'[Date] = LatestDate
                && 'Table 2'[ID] = EARLIER ( 'Table 1'[ID] )
        )
    )

BTW, .pbix file attached.

Best Regards,

Icey

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

[amitchandak]

@mober ,

latest location = LastNONBLANKVALUE(table2[Date], MAx(Table2[Location]))

make sure two tables have joins to display data together

[mober]

@amitchandak 

I am getting the same value for all IDs, even though the two tables are joined on the ID

[AllisonKennedy]

As a MEASURE you could do this and use in a matrix visual: 

Latest Location = 

VAR _lastDate = MAX(Table2[Date])

RETURN MAXX(FILTER(Table2, Table2[Date]=_lastDate), Table2[Location]) 

or

Latest Location = 

VAR _lastDate = MAX(Table2[Date])

RETURN CALCULATE(SelectedValue[Table2[Location]), (FILTER(Table2, Table2[Date]=_lastDate))

[mober]

Thank you for your solution @AllisonKennedy .

However, I will need the value in a column in order for the data to interact correctly between multiples tables.

[AllisonKennedy]

The same formula could work as column, but your cross filter direction will be preventing it from pulling the info through properly. It would need to be set to both.

[mober]

@AllisonKennedy The cross filter direction is already set to "Both", but I am receiving blank rows when using the formulas you provided.

[AllisonKennedy]

That's probably because it is a many to many relationship. I suggest you to look again at your data model and get that set up properly before you can begin to solve problems using DAX.
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-many-to-many-relationships