Match Different Rows From Same Table In New Calculated Column

[heiligbd]

Good Day,

So I have a dataset that is looking at inventory metrics at multiple plants. For example, I have determined where I have a plant that is the Primary consumer of a particular material at one plant and therefore in an effort to maximize material consumption, other plants could transfer their like material to them if they are not meeting certain criteria. In the example below, I would like help solving for the "Transfer To" Column by particular material filtered. So that the other plants would know whom to send their materials to get consumed and reduce overall inventory. All of the fields shown are available and I would like a DAX solution to help on this. 

Thanks in advance for your help!

Plant	Material	Rank Consumption	Rank Inv Turns	Transfer	Primary	Transfer To
A	123	1	1	NA	Primary	NA
B	123	4	3	Transfer	Secondary	A
C	123	2	4	Transfer	Secondary	A
D	123	3	2	Transfer	Secondary	A
A	456	2	3	Transfer	Secondary	B
B	456	1	1	NA	Primary	NA
C	456	4	2	Transfer	Secondary	B
D	456	3	4	Transfer	Secondary	B

[heiligbd]

Awesome! Thanks for the help!

[MFelix · Accepted solution]

Hi @heiligbd ,

Not sure if you want a calculated column or a measure but here is the values for both:

Calculated column:

TransferTo =
IF (
    'Table'[Transfer] <> "NA",
    MAXX (
        TOPN (
            1,
            FILTER ( 'Table', 'Table'[Material] = EARLIER ( 'Table'[Material] ) ),
            'Table'[Rank Inv Turns], ASC
        ),
        'Table'[Plant]
    ),
    'Table'[Transfer]
)

Measure:

TransferTo _ = 
    IF(SELECTEDVALUE('Table'[Transfer]) <> "NA",MAXX (
        TOPN (
            1,
            FILTER ( ALLSELECTED('Table'[Material], 'Table'[Rank Inv Turns],'Table'[Plant]), 'Table'[Material] = MAX ( 'Table'[Material] ) ),
            'Table'[Rank Inv Turns], ASC
        ),
        'Table'[Plant]
    ), SELECTEDVALUE('Table'[Transfer]))