Forum Discussion

Rogerh's avatar
Rogerh
Icon for Helper II rankHelper II
6 years ago
Solved

Lookup another table with Multiple Values, if one value is correct report back

Good morning all,   I need some help please! I have two tables, Orders and Product Builder; The order table has a single line for each order. The Product table is the order broken down by each line...
Need Help
  • Pragati11's avatar
    Pragati11
    6 years ago

    Hi Rogerh ,

     

    I think you missed my last response. Try using following dax:

    Try something like this:
    Delivery Charge = Var lookup = CALCULATE(FIRSTNONBLANK('Platform - Pro Builder'[supplier_id], 1), FILTER(ALL('Platform - Pro Builder')'Platform - Pro Builder'[order_id] = 'Platform - Orders'[order_id]))
    Var checkVal = 987
    RETURN
    IF( lookup = checkVal, "YES", "NO")
     
    Thanks,
    Pragati
Pragati11's avatar
Pragati11
Icon for Super User rankSuper User
6 years ago

Hi Rogerh ,

 

I am assuming that both your tables are having a relationship on "ORDER ID" column.

 

You can try something as below:

NewDeliveryCol =

Var lookup = CALCULATE(VALUES('Product Builder'[Supplier]), FILTER(Product Builder, Product Builder[Order ID] = Order Table[Order ID]))

Var checkVal = 987

RETURN

IF( lookup = checkVal, "YES", "NO")

 

I haven't checked the syntax of above DAX, so let me know what output it gives and if there are any errors then what errors.

 

Thanks,

Pragati

 

 

 

  • Rogerh's avatar
    Rogerh
    Icon for Helper II rankHelper II
    6 years ago

    Hi Pragati11 

     

    Thank you for your reply, I have tried the below but I get an error: A Table of multipule Values was supplier where a single value was exspected. Do you know how i can get around this please?

     

    I updated your code to the below:

    Delivery Charge = Var lookup = CALCULATE(VALUES('Platform - Pro Builder'[supplier_id]), FILTER('Platform - Pro Builder', 'Platform - Pro Builder'[order_id] = 'Platform - Orders'[order_id]))
    Var checkVal = 987
    RETURN
    IF( lookup = checkVal, "YES", "NO")
     
    Thanks
    • Pragati11's avatar
      Pragati11
      Icon for Super User rankSuper User
      6 years ago

      Hi Rogerh ,

       

      Try modifying the dax as follows:

      Delivery Charge = Var lookup = LOOKUPVALUE('Platform - Pro Builder'[supplier_id], 'Platform - Pro Builder'[order_id], 'Platform - Orders'[order_id])
      Var checkVal = 987
      RETURN
      IF( lookup = checkVal, "YES", "NO")
       
      This should resolve the issue.
       
      Thanks,
      Pragati
      • Rogerh's avatar
        Rogerh
        Icon for Helper II rankHelper II
        6 years ago

        Hi Pragati11 

        Thanks again but i am getting the same error - A Table of Multiple Values was supplierd where a single value was exspected.

         

        I think this is due to the Pro Builder Table having the Order_ID multipul times.  Is there another way around you can think of please? Cheers