Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
2 years ago
Solved

Compare Two Columns

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]
)
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

2 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    2 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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