LOOKUPVALUE with multiple values error

[jobf]

I tried to do LOOKUPVALUE to create a new column from the results of another table, but it gave an error due to the fact that this other table presents multiple values.

The code is as follows:

'Talhão'[DT_OPERACAO],
'Talhão'[CD_UPNIVEL3],
BD_INS002[CD_UPNIVEL3]
)

The problem is that the 'Talhão'[DT_OPERACAO] column has multiple values ​​in this code. Is there any way to select only the smallest or first value?

[v-jtian-msft · Accepted solution]

Hi,@jobf .Hello,@ryan_mayu ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.

According to your description, you want to create a new field value in another table based on a field in one table.

But you encountered a problem: when the target table has more than one value corresponding to the first table, you will get an error if you use the LOOKUPVALUE function directly, and you would like to create the new column with the smallest of the multiple values if there are more than one corresponding value.

If my understanding is correct, you can refer to my test below.

Suggestion 1. Continue to use the LOOKUPVALUE function.
like this:

C_Sales02 =
LOOKUPVALUE (
    'Sales2'[SaleNum],
    'Sales2'[SalesDate], 'Expenses'[SalesDate],
    'Sales2'[SaleNum],
        CALCULATE (
            MIN ( 'Sales2'[SaleNum] ),
            FILTER ( 'Sales2', 'Sales2'[SalesDate] = 'Expenses'[SalesDate] )
        )
)

LOOKUPVALUE to get the corresponding value, you can see that when the date corresponds to some of the data in the Sale02 table there are more than one corresponding value: 6/1/2024 and 6/2/2024 each have two corresponding values, if there are more than one corresponding value will perform the following judgment:    

'Sales2'[SaleNum],
        CALCULATE (
            MIN ( 'Sales2'[SaleNum] ),
            FILTER ( 'Sales2', 'Sales2'[SalesDate] = 'Expenses'[SalesDate] )
        )

suggestion 2.use MIN /MINX  function
like this:
The computational environment of the code is affected by the row context (the current row) since a computational column is created

C_Sales2min =
CALCULATE (
    MIN ( 'Sales2'[SaleNum] ),
    FILTER ( 'Sales2', 'Sales2'[SalesDate] = Expenses[SalesDate] )
)

Please note: the blank part of the calculation column is to simulate the Sales2 table does not exist in the Expenses corresponding to the data, when the calculation of the row context is empty, so the return of the results of the calculation of the empty, not the code itself, but the problem of the data.
You can also use the MINX function provided by @ryan_mayu to realize the following
like this:

C_Sales2minX =
MINX (
    FILTER ( 'Sales2', 'Sales2'[SalesDate] = 'Expenses'[SalesDate] ),
    'Sales2'[SaleNum]
)

tables relationship:

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu]

maybe you can try this

=mixx(filter(table2, table1[column]=table2[column], table2[outputcolumn])