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

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

[v-ljerr-msft]

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]

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]

Hi @Anonymous,

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

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

Regards

[Anonymous]

Thank you so much for your help.

I have used the formlar as adviced, I can now display the measures but

It didnt display/return measures  "Result1" and "Result3".

This two are the duplicates in both EUA and Mnemonic.

As stated, "Result1" and "Result3 " are feeding from  different third table hence they should display.

I've tried the merging, creation of the third table  and made connections but still no success.

I've sent the current Pbix to your email

Please help

[Ashish_Mathur]

Hi,

Share the link from where i can download your file.  Also, show the expected result.

[v-ljerr-msft]

Hi @Anonymous,

---

It didnt display/return measures  "Result1" and "Result3".

This two are the duplicates in both EUA and Mnemonic.

As stated, "Result1" and "Result3 " are feeding from  different third table hence they should display.

---

In this scenario, an alternative solution is showing the duplicated values in the same row with a delimiter(i.e. a comma). The formula below is for your reference. 

Station = 
CONCATENATEX (
    CALCULATETABLE (
        VALUES ( STATION[STATION] ),
        FILTER (
            STATION,
            STATION[EUA] = RELATED ( A2011_Device[EUA] )
                && STATION[Mnemonic] = A2011_Config[Mnemonic]
        )
    ),
    [STATION],
    ","
)

Regards

[Anonymous]

Thank you so much .

The delimeter do not do any justice to the display or output as they are different values. Also, The delimeter has even made the slicers mumb and inactive.

See below What is expected.