Power BI get the last NR using DAX

Question

I have two tables. First table with duplicate material numbers and an 'NR2' Column and the second table with only 1 material number and also an 'Nr' Column.

I need to display the 'NR2' from the first table 'Export' in the 'overview' table, but only the most recent (don't have a date). The last letter should be copied into the second table 'overview'. Each material number should have 2 letters at the end, hope the picture explains it. Thanks!

Export table:

Material	NR 2
123	X
123	Y
123	Z
456	K
456	L
456	M1
789	-
789	A
789	D

Overview table: NR 2 does not have to be the same number as NR 1

Material	NR 1	NR2 From Export
123	X	Z
456	K	M1
789	D	D

Answer 1

Power Query

1. On your Overview query, merge with Export on Material column.
2. Expand the new column after transforming it to the last row.

Example:

let
  Source = YourSourceOrPreviousStep,
  #"Merged queries" = Table.NestedJoin(Source, {"Material"}, Export, {"Material"}, "Export", JoinKind.LeftOuter),
  #"Expanded Export" = Table.ExpandTableColumn(Table.TransformColumns(#"Merged queries", {"Export", each Table.LastN(_, 1)}), "Export", {"NR 2"}, {"NR 2"})
in
  #"Expanded Export"

---

DAX
If you want to do it in DAX then you will need to add an Index column to the Export table via Power Query so that it has some form of ordering (as you don't have a date column).

1. In Power Query, add an Index column to your Export table.

2. Then with DAX you can create a Calculated Column with:

// if there isn't a relationship
NR 2 = 
  CALCULATE(
    MAX('Export'[NR 2]),
    TOPN(1, FILTER('Export', [Material] = Overview[Material]), [Index], DESC)
  )

// if there is a relationship
NR 2 = 
  CALCULATE(
    MAX('Export'[NR 2]),
    TOPN(1, CALCULATETABLE('Export'), [Index], DESC)
  )

Answer 2

you can try to create an index column in PQ

then create a column in the second table.

Column =
VAR _max =
    MAXX (
        FILTER ( 'Export', 'Table'[Material] = 'Export'[Material] ),
        'Export'[Index]
    )
RETURN
    MAXX (
        FILTER (
            'Export',
            'Export'[Material] = 'Table'[Material]
                && 'Export'[Index] = _max
        ),
        'Export'[NR 2]
    )