CODEWITHSUNDEEP
powerbidax
Motu61
Motu61

Reputation: 17

Looking up a value from a set of duplicates to be assigned to a unique value

I am working on a project where I have a table A with unique serial numbers and a separate table B with duplicates of serial numbers and customer locations. I am trying to pair the customer locations from table B to each unique serial number that I have listed in table A.

Originally I was trying to use the LOOKUPVALUE function but I continued to get the error "A table of multiple values was supplied where a single value was expected".

Screenshot

Upvotes: 1

Views: 4080

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

You probably have the same serial number in multiple departments, so the LOOKUPVALUE function doesn't know which department you want to return in that case.

Try using the following to identify which serial numbers are associated with multiple departments.

LocationCount =
    COUNTROWS(
        CALCULATETABLE(
            VALUES('Asset List'[Customer_Department]),
            FILTER(
                 'Asset List',
                 'Asset List'[Asset_Serial_Num] = 'Usage Calculations'[Serial Number]         )
        )
    )

You should be able to filter LocationCount > 1.

If you just want to pick one value, then you can use MAX or MIN instead of LOOKUPVALUE. E.g.

Location =
        CALCULATE(
            MAX('Asset List'[Customer_Department]),
            FILTER(
                'Asset List',
                'Asset List'[Asset_Serial_Num] = 'Usage Calculations'[Serial Number]
            )
        )

FIRSTNONBLANK and LASTNONBLANK would probably also work.

Upvotes: 1

Related Questions