Multiple Values Needed

[Anonymous]

Hello!

Hoping the big brains on here can help me 🙂

Here is an example of my data:

ProductID	Facility	Sku	SkuFacility	MadeInFacility	TransferFrom	SkuTransferFrom
6	A	1234	1234A	Y
7	B	1234	1234B	N	A	1234A
8	C	1234	1234C	N	A	1234A
9	D	1234	1234D	Y

I want to get a final column that looks like this:

ProductID	Facility	Sku	SkuFacility	MadeInFacility	TransferFrom	SkuTransferFrom	TransferTo
6	A	1234	1234A	Y			BC
7	B	1234	1234B	N	A	1234A
8	C	1234	1234C	N	A	1234A
9	D	1234	1234D	Y

I tried using this expression:

TEST = LOOKUPVALUE(Table[Facility],Table[SkuTransferFrom],Table[SkuFacility])

and got the error: A table of multiple values was supplied where a single value was expected.

Well, I want the multiple values please!!!

Thank you for your help

Jackie

[jtownsend21]

@Anonymous  Are you saying that there are instances where there should be more than one id in the TransferTo field? 

[Anonymous]

@jtownsend21 Yes, as in the example above the item transfers to two facilities (B and C) from A.

[Anonymous]

@Anonymous 

Can you use a measure or must it be a column?

Here is a measure:

TransferTo = 
calculate(
    CONCATENATEX(
        VALUES('Table'[Facility]);
        'Table'[Facility];
        ""
    );
    filter(
        all('Table');
        'Table'[TransferFrom]=SELECTEDVALUE('Table'[Facility])
    )
)

you can probably make something similar as a column if needed 🙂

[Anonymous]

@Anonymous Thank you for your help!  Unfortunately I am getting the result of ABCD instead of just BC for this particular SKU.  Any ideas?  Thanks again!

[Anonymous · Accepted solution]

Are you sure? did you create a new column or new measure? this returns BC when used as a measure for me.

Anyway, for a column try this: 

TransferTo1 = 
calculate(
    CONCATENATEX(
        VALUES('Table'[Facility]);
        'Table'[Facility];
        ""
    );
    filter(
        all('Table');
        'Table'[TransferFrom]=EARLIEST('Table'[Facility])
    )
)