Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Error with Lookup and Merge 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. 

Also

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 same  below errors:

 

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

 

Please what is the ideal formular or workaround??

 

Many Thanks

 

Need Help

7 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Anonymous,

     

    I find that you have posted the same question here previously, and Simon has provided a solution. Have you tried the solution? Does it work in your scenario?

     

    Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi v-ljerr-msft,

       

      I have tried all Simon suggested initially.. Later sent him the PBix file, he  diagnosed the problem like I have done and still did'nt give any solution. 

       

      See below my trial of what Simon adviced initally.

       

       

      """

      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 same  below errors:

       

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

       

      Please what is the ideal formular or workaround??""""

       

       

       

       

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi Anonymous,

         

        Try the formula below, it should work in your scenario. :smileyhappy:

        Station = 
CALCULATE (
    FIRSTNONBLANK ( STATION[STATION], 1 ),
    FILTER (
        STATION,
        STATION[EUA] = RELATED ( A2011_Device[EUA] )
            && STATION[Mnemonic] = A2011_Config[Mnemonic]
    )
)

         

        Regards