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 ,

 

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

 

 

  • Pragati11's avatar
    Pragati11
    Icon for Super User rankSuper User
    6 years ago

    Hi Rogerh ,

     

    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
    • Rogerh's avatar
      Rogerh
      Icon for Helper II rankHelper II
      6 years ago

      Hi Pragati11 

       

      Thank you! Will this work?

       

      Platform - Orders  Column We are working on
      Order_ID  Delivery Charge
      123  Yes
      124  Yes
      125  No

       

      Platform - Pro Builder  
      Pro_Builder_IDOrder_IDSupplier_ID
      455123987
      454123974
      453123985
      452124987
      451124987
      450124954
      449125974
      448125985
      • Pragati11's avatar
        Pragati11
        Icon for Super User rankSuper User
        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