Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
fbron
Frequent Visitor

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 weight from the Warehouse Lines 

 

I created a string to connect both tables [Combined]

Since the relationship is 1vsmany LOOKUPVALUE doesnt seem to be the way to go.

 

This is my current situation:

The "Combined" column is where both tables connect.

fbron_0-1669813551179.png 

 

 

Is there a way to make LOOKUPVALUE work in this scenario?

 

 

 

 

 

8 REPLIES 8
mangaus1111
Solution Sage
Solution Sage

Hi @fbron ,

If you remove the duplicate from the table 'Warehouse line', it should work.

Try to create a temporary table using 

ADDCOLUMNS(

    DISTINCT('Warehouse line'[Combined]),

   "Weight", MAX(Warehouse line'[Weight])

)

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@mangaus1111 Thank you for the reply.

 

Followed your suggestion but this resulted in the max weight within the warehouse rowes in each row of the calculated table.  

 

The idea of a calculated table is something that can work for me. Based on the warehouse lines the preferred outcome would be something like this:

fbron_0-1669824066067.png

 

Nishudhan
Frequent Visitor

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

 

 

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\

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.

 

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)

@fbron , A new column

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

 

or

 

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

@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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.