Power BI - Getting the most recent value from a related table

user_10887133 0
Viewed 615

I know this must be extremely simple, but every example I can find online only works within a single table. I've simplified my situation to these two tables:

enter image description here

I want to add a calculated column to the first table, showing the most recent value for that id. It also needs to work with text.

dax powerbi
1 Answers

There are a variety of ways to do this kind of thing as I've explained before and all of the solutions there can be adjusted to work in this case.

Doing this as a calculated column and with a second table, you need to make sure you are using row context and filter context appropriately.

Here's are a couple different possibilities I think may work:

MostRecentValue =
MAXX ( TOPN ( 1, RELATEDTABLE ( Table2 ), Table2[date] ), Table2[value] )

In this one, RELATEDTABLE is doing the work of filtering Table2 to only the rows where id matches Table1.

MostRecentValue =
VAR PrevDate = CALCULATE ( MAX ( Table2[date] ) )
RETURN CALCULATE ( MAX ( Table2[value] ), Table2[date] = PrevDate )

The relationship is more subtle here. Wrapping the MAX in CALCULATE forces a context transition so that the row context (which includes id) is applied to Table2 as filter context.

user_7652260
Related