Output Data from three different tables

[Anonymous]

Hi,

The displayed data are supposed to be different values as they are feeding differently from three different tables.

Ther are supposed to display measures 'Result1 - Result5'.

CATALOGUE, AREA and HOSE are different tables .

See field relation below:

CATALOGUE	AREA	HOSE	POPOUT
KAY1	DAK1	TOG1	Result1
KAY1	DAK2	TOG2	Result2
KAY1	DAK1	TOG2	Result3
KAY2	DAK2	TOG2	Result4
KAY2	DAK1	TOG2	Result5

I've used the LOOKUP formular:

StationID = LOOKUPVALUE(POPOUT[POPOUT],  POPOUT[AREA], [AREA], POPOUT[CATALOGUE], RELATED(Table6[CATALOGUE)) 

I get the error message:

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

This LOOKUP formular has worked  in projects where it is just referencing two tables but this is referencing three tables.

This project has an extra field 'HOSE' and have different values  TOG1 and TOG2.. which is different from the duplicate KAY1-DAK1 . This means Result 1 and Result3 are supposed to return different values.

I understand, Lookupvalue returns more than one value and do not accept duplicates hence the error and not appropriate in this circumstance. 

Please what is the ideal formular or workaround??

Many Thanks

[v-sihou-msft]

@Anonymous

When using LOOKUPVALUE() function, the lookup column and value must be 1 to 1 mapping. And it's not possible to involve three tables.

In this scenario, I suggest you build relationships between those tables with those lookup columns. Then you can use "Merge Queries" to join the expected column into a single dataset. 

Regards,

[Anonymous]

Hi,

I've made the relationships between those tables with those lookup columns.

Edit Queries - Merge Queries - Merge Queries ->

I then merged matching columns from my tables.

I have used 'Left Outer(all from first, matching from second)' and 'Full Outer (all rows from both)' and Inner Join..

But I keep getting the below errors:

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

I've sent the PBIx file to your email.

Please Help

[v-sihou-msft]

@Anonymous

I can only view two tables values from your .pbix due to permission. I noticed that you join same table twice in Mnemonic table. If this is the "POPOUT" table, you can expand table to get expected columns after the "Merged Queries" step. Then merge all three CATALOGUE, AREA, HOSE table and expand columns for your LOOKUP.

As you said, you keep getting " A table of multiple values was supplied where a single value was expected." error. This error means the value you are going to mapping has multiple values. Since you already build the relationship between those tables, when doing "Merge Query", you should select the connecting columns for built relationship, which means at list one column is with unique values ("One" side). 

But, it still may not work. For exmaple, if POPOUT then "ONE" side in your relationship, and you have One-To-Many to all other three tables. So all three tables join into POPOUT table, appear as a Table object on row level. If you want to mapping values from "Many" side tables each other, you may never get the an unique loopup value for a single entry. 

The workaround I suggest is to include the column from the third table into your first table. Then it still became a "Two Table" scenario for your LOOKUPVALUE() function. 

Regards,