Forum Discussion

fbron's avatar
fbron
Frequent Visitor
3 years ago

Lookupvalue doesnt return expected values

Table 1=  Sales Line Table with rows with a certain weigth. Table 2 = Warehouse Line table with a certain weight What I wat to do is create a column in my Sales Line Table (Table 1) containing the ...
Need Help
Nishudhan's avatar
Nishudhan
Frequent Visitor
3 years ago

Can you please try lookvalue() in the table which has many side of the relationship...it will work

 

 

  • fbron's avatar
    fbron
    Frequent Visitor
    3 years ago

    Nishudhan, 

     

    Thank you for the quick response. 

    Unfortunately im in need for the column to be in the sales table. 

     

    Basically what im looking for is that all the wieght from a certain Sales Line is represented in our warehous (warehouse line) the visuals Could be possible that theres no line available in the Warehouse Line Table\

    • amitchandak's avatar
      amitchandak
      Super User
      3 years ago

      fbron , A new column

      sumx(filter(Warehouse,Warehouse[Combine] = sales[combine] ) , Warehouse[Weight])

       

      or

       

      Maxx(filter(Warehouse,Warehouse[Combine] = sales[combine] ) , Warehouse[Weight])

      • Nishudhan's avatar
        Nishudhan
        Frequent Visitor
        3 years ago

        amitchandak  

        Hi amit,

        The same will throw an error stating 

        "A single value for column 'combined' in table 'Warehouse_line' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result." 

        So it will not working as it is aggregating the result.

         

    • Nishudhan's avatar
      Nishudhan
      Frequent Visitor
      3 years ago

      Hi fbron ,

       

      I think LOOKUPVALUE() will work because you want weight from sales to warehouse table which is having many side of the relationship.  And LOOKUPVALUE() will work in the case when you want result in the many side of the relationship from one side of the relationship.

       

      • fbron's avatar
        fbron
        Frequent Visitor
        3 years ago

        The thing is, I need it to be the other way around.

         

        I've got Weight in my sales line and I like to add a column to those sales lines with the sum off weight from my warehouse lines. 

         

        End goal is to check if all weight in my sales lines is represented within the warehouse table. 

        (within ERP the warehouse lines are generated from the sales lines which is a manual proces so there is a possibility that there is a discrapency between the 2. Sales line being the main table)