Why it says a table of multiple values was supplied

[newgirl]

Hello. 

I have my main sales table (tbl_phrawsales) which essentially has Invoice Date, volume, client, Region, etc. and there are calculated columns I added to look up from other tables.

So what I wanted for this calculated column is that if the invoice date is before June 1, 2022, then it should follow the _before variable. Else, it should follow the _after variable. 

The difference only between the 2 variables is that there are less conditions in the _after one. So I can't figure out why it doesn't want to display the results.

If I make the RETURN as _before, it shows the results but if I make RETURN as _after, it shows the same error. Can somebody help me?

MainRegion = 
VAR _before =
IF (
    'CR tbl_phrawsales'[Market] = "ILOILO"
        || 'CR tbl_phrawsales'[Market] = "NEGROS OCCIDENTAL"
        || 'CR tbl_phrawsales'[Market] = "SURIGAO DEL NORTE"
        || 'CR tbl_phrawsales'[Market] = "SOUTH LUZON OFFSHORE"
        || 'CR tbl_phrawsales'[Market] = "SULU",
    LOOKUPVALUE (
        'Duplicate Micromarkets'[Market],
        'Duplicate Micromarkets'[Merged_ConsMicro], 'CR tbl_phrawsales'[Merged_CityMarket]
    ),
    LOOKUPVALUE (
        'Full Mapping'[MARKET],
        'Full Mapping'[MICROMARKET], 'CR tbl_phrawsales'[Market]
    )
)
VAR _after = 
IF (
     'CR tbl_phrawsales'[Market] = "SOUTH LUZON OFFSHORE"
        || 'CR tbl_phrawsales'[Market] = "SULU",
    LOOKUPVALUE (
        'Duplicate Micromarkets'[Market],
        'Duplicate Micromarkets'[Merged_ConsMicro], 'CR tbl_phrawsales'[Merged_CityMarket]
    ),
    LOOKUPVALUE (
        'Full Mapping'[MARKET],
        'Full Mapping'[MICROMARKET], 'CR tbl_phrawsales'[Market]
    )
)
RETURN IF('CR tbl_phrawsales'[Invoice date] < DATE(2022,6,1), _before, _after)

[v-yalanwu-msft]

Hi, @newgirl ;

Is your problem solved? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi， @newgirl ；

Try it.

MainRegion =
VAR _before =
    IF (
        'CR tbl_phrawsales'[Market] = "ILOILO"
            || 'CR tbl_phrawsales'[Market] = "NEGROS OCCIDENTAL"
            || 'CR tbl_phrawsales'[Market] = "SURIGAO DEL NORTE"
            || 'CR tbl_phrawsales'[Market] = "SOUTH LUZON OFFSHORE"
            || 'CR tbl_phrawsales'[Market] = "SULU",
        CALCULATE (
            FIRSTNONBLANK ( 'Duplicate Micromarkets'[Market], TRUE () ),
            FILTER (
                ALLSELECTED ( 'Duplicate Micromarkets' ),
                [Merged_ConsMicro] = 'CR tbl_phrawsales'[Merged_CityMarket]
            )
        ),
        CALCULATE (
            FIRSTNONBLANK ( 'Full Mapping'[MARKET], TRUE () ),
            FILTER ( 'Full Mapping', [MICROMARKET] = 'CR tbl_phrawsales'[Market] )
        )
    )
VAR _after =
    IF (
        'CR tbl_phrawsales'[Market] = "SOUTH LUZON OFFSHORE"
            || 'CR tbl_phrawsales'[Market] = "SULU",
        CALCULATE (
            FIRSTNONBLANK ( 'Duplicate Micromarkets'[Market], TRUE () ),
            FILTER (
                ALLSELECTED ( 'Duplicate Micromarkets' ),
                [Merged_ConsMicro] = 'CR tbl_phrawsales'[Merged_CityMarket]
            )
        ),
        CALCULATE (
            FIRSTNONBLANK ( 'Full Mapping'[MARKET], TRUE () ),
            FILTER ( 'Full Mapping', [MICROMARKET] = 'CR tbl_phrawsales'[Market] )
        )
    )
RETURN
    IF ( 'CR tbl_phrawsales'[Invoice date] < DATE ( 2022, 6, 1 ), _before, _after )

Lookupvalue sould return error..because  'CR tbl_phrawsales'[Merged_CityMarket] have duplicate data.
if you want to pull out the value from 'CR tbl_phrawsales' to 'Duplicate Micromarkets'  then your have to take first value.

https://community.powerbi.com/t5/Desktop/LOOKUPVALUE-quot-A-table-of-multiple-values-was-supplied-wh...

https://community.powerbi.com/t5/Desktop/LOOKUPVALUE-A-table-of-multiple-values-was-supplied-where-a...

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@newgirl , if your lookups are returning more than one value it will give that error

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8