cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ichebobo Member
Member

Output Data from three different tables

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

3 REPLIES 3
Moderator v-sihou-msft
Moderator

Re: Output Data from three different tables

@ichebobo

 

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,

ichebobo Member
Member

Re: Output Data from three different tables

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

Moderator v-sihou-msft
Moderator

Re: Output Data from three different tables

@ichebobo

 

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.

 

5.PNG

 

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,