Compare Two Columns

[Syndicate_Admin]

Hello everyone!
I'm working with 2 tables: The first one has all the information about the products, including the product_id. The second one has the information of the orders that my store has received, including the product_id and price. I'm trying to compare both tables, using column product_id, and bring the price from the second table to the first, I see this error message:

We cannot determine a single value for column 'product_id' in the 'olist_products_dataset' table. This can occur when a measure formula references a column that contains many values without specifying an aggregation, such as min, max, count, or sum, to obtain a single result.

Here's the formula I'm using:

Measure 3 = LOOKUPVALUE(

olist_order_items_dataset[price],olist_order_items_dataset[product_id],olist_products_dataset[product_id]

)

[v-jialongy-msft · Accepted solution]

@ryan_mayu Thanks for your prompt reply!

Hi @Syndicate_Admin 

Please consider using an aggregation function to derive a single price value. For example, you might want to use the average, minimum, or maximum price.Here's how you could modify your measure to use the average, minimum, or maximum price.

AVERAGE

Measure 3 = 
AVERAGEX(
    FILTER(
        olist_order_items_dataset,
        olist_order_items_dataset[product_id] = olist_products_dataset[product_id]
    ),
    olist_order_items_dataset[price]
)

MAX

Measure 3 = 
MAXX(
    FILTER(
        olist_order_items_dataset,
        olist_order_items_dataset[product_id] = olist_products_dataset[product_id]
    ),
    olist_order_items_dataset[price]
)

MIN

Measure 3 = 
MINX(
    FILTER(
        olist_order_items_dataset,
        olist_order_items_dataset[product_id] = olist_products_dataset[product_id]
    ),
    olist_order_items_dataset[price]
)

Best Regards,

Jayleny

[ryan_mayu]

that means one product_id can find different prices in the second table, so can't determine which price to take. pls share the sample data and expected output. Maybe we can use other formula to solve this.