Lookupvalue- A table of multiple values was supplied where a single value was expected.

[Saxon10]

Hi,
I have two tables: 'data' and 'report'. The 'data' table comprises columns such as State, City, Mode, Ship-To, Phone, and Location. Meanwhile, the 'report' table contains columns including Item, State, City, Mode, and Ship-To.

The relationship between these tables is established through shared attributes—City, Mode, and Ship-To. 
I aim to retrieve the Phone and Location information from the 'data' table based on matches found in the 'City', 'Mode', and 'Ship-To' columns from the 'report' table.
I attempted to utilize the lookupvalue function but encountered an error message. "A table of multiple values was supplied where a single value was expected."

PHONE(DESIRED RESULT-I) = LOOKUPVALUE(DATA[PHONE],DATA[CITY],REPORT[CITY],DATA[MODE],REPORT[MODE],DATA[SHIP TO],REPORT[SHIP TO])

LOCATION(DESIRED RESULT-I) = LOOKUPVALUE(DATA[LOCATION],DATA[CITY],REPORT[CITY],DATA[MODE],REPORT[MODE],DATA[SHIP TO],REPORT[SHIP TO])
I am seeking a DAX formula to generate the desired outcome by creating a new calculated column.

Report Table:

Report Table:

ITEM	CITY	MODE	SHIP TO	PHONE(DESIRED RESULT)	LOCATION(DESIRED RESULT)
12	C1	AIR	A010-C050	A01	A010
13	C1	AIR	A010-C050	A01	A010
14	C1	AIR	A010-C050	A01	A010
15	C1	AIR	A010-C050	A01	A020
16	C1	AIR	A010-C050	A01	A020
17	C1	AIR	A010-C050	A01	A020
18	C1	AIR	A010-C050	A01	A030
19	C1	AIR	A010-C050	A01	A030
20	C1	AIR	A010-C050	A01	A030
21	C1	AIR	A010-C050	A01	A040
22	C1	AIR	A010-C050	A01	A040
23	C1	AIR	A010-C050	A01	A040
24	C1	AIR	A010-C050	A01	A050
25	C1	AIR	A010-C050	A01	A050
26	C1	AIR	A010-C050	A01	A050
27	C2	AIR	A060-C100	A01	A060
28	C2	AIR	A060-C100	A01	A060
29	C2	AIR	A060-C100	A01	A060
30	C2	AIR	A060-C100	A01	A070
31	C2	AIR	A060-C100	A01	A070
32	C2	AIR	A060-C100	A01	A070
33	C2	AIR	A060-C100	A01	A080
34	C2	AIR	A060-C100	A01	A080
35	C2	AIR	A060-C100	A01	A080
36	C2	AIR	A060-C100	A01	A090
37	C2	AIR	A060-C100	A01	A090
38	C2	AIR	A060-C100	A01	A090
39	C2	AIR	A060-C100	A01	A100
40	C2	AIR	A060-C100	A01	A100
41	C2	AIR	A060-C100	A01	A100
42	C1	TRAIN	T010-C050	T01	T010
43	C1	TRAIN	T010-C050	T01	T010
44	C1	TRAIN	T010-C050	T01	T010
45	C1	TRAIN	T010-C050	T01	T020
46	C1	TRAIN	T010-C050	T01	T020
47	C1	TRAIN	T010-C050	T01	T020
48	C1	TRAIN	T010-C050	T01	T030
49	C1	TRAIN	T010-C050	T01	T030
50	C1	TRAIN	T010-C050	T01	T030
51	C1	TRAIN	T010-C050	T01	T040
52	C1	TRAIN	T010-C050	T01	T040
53	C1	TRAIN	T010-C050	T01	T040
54	C1	TRAIN	T010-C050	T01	T050
55	C1	TRAIN	T010-C050	T01	T050
56	C1	TRAIN	T010-C050	T01	T050
57	C2	TRAIN	T060-C100	T01	T060
58	C2	TRAIN	T060-C100	T01	T060
59	C2	TRAIN	T060-C100	T01	T060
60	C2	TRAIN	T060-C100	T01	T070
61	C2	TRAIN	T060-C100	T01	T070
62	C2	TRAIN	T060-C100	T01	T070
63	C2	TRAIN	T060-C100	T01	T080
64	C2	TRAIN	T060-C100	T01	T080
65	C2	TRAIN	T060-C100	T01	T080
66	C2	TRAIN	T060-C100	T01	T090
67	C2	TRAIN	T060-C100	T01	T090
68	C2	TRAIN	T060-C100	T01	T090
69	C2	TRAIN	T060-C100	T01	T100
70	C2	TRAIN	T060-C100	T01	T100
71	C2	TRAIN	T060-C100	T01	T100

Data Table:

Data Table:

STATE	CITY	MODE	SHIP TO	PHONE	LOCATION
C010	C1	TRAIN	T010-C050	A01	T010
C020	C1	AIR	A010-C050	A01	A020
C030	C1	AIR	A010-C050	A01	A030
C040	C1	AIR	A010-C050	A01	A040
C050	C1	AIR	A010-C050	A01	A050
C060	C2	AIR	A060-C100	A01	A060
C070	C2	AIR	A060-C100	A01	A070
C080	C2	AIR	A060-C100	A01	A080
C090	C2	AIR	A060-C100	A01	A090
C100	C2	AIR	A060-C100	A01	A100
C010	C1	AIR	A010-C050	A01	A010
C060	C2	TRAIN	T060-C100	T01	T060
C030	C1	TRAIN	T010-C050	T01	T030
C040	C1	TRAIN	T010-C050	T01	T040
C050	C1	TRAIN	T010-C050	T01	T050
C020	C1	TRAIN	T010-C050	T01	T020
C070	C2	TRAIN	T060-C100	T01	T070
C080	C2	TRAIN	T060-C100	T01	T080
C090	C2	TRAIN	T060-C100	T01	T090
C100	C2	TRAIN	T060-C100	T01	T100

https://www.dropbox.com/scl/fi/ow27nnsst5pjh5566bxad/ZBS-LOOKUP-MULTIPLE-VALUE-SUPPLY-02.11.23.pbix?...

[ryan_mayu]

why the first line location is also A010 ? what's the logic of matching?

[Saxon10]

@ryan_mayu , Thanks for yor reply. The logic is It's a combination of the columns City, Mode, and Ship-To from both tables.

[ryan_mayu]

let's make it be clearer

for the same city,mode and shipto which I highlighted. We can find A010,A020,A030,A040,A050.

why you set A010 for item 12-14 and A020 for item 15-17? Each location is set for 3 times? what if we have more than 3? How to allocate these locations?