2

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.

yaxx0r
  • 149
  • 3
  • 15

1 Answers1

4

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.

Alexis Olson
  • 38,724
  • 7
  • 42
  • 64