cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NickProp28
Post Partisan
Post Partisan

Need help on function VLOOKUP

Dear Community,

 

I would like to create a new column to sum up the total weight from another table. But I dont want the total value are copying to every single row in my new table. I just want to show the value in only one of the ID.

My expectation: 
Table 1 

NickProp28_0-1629711561819.png

New table (vlookup) - Based on the ID

NickProp28_1-1629711572738.png

 

Thank for your attention. Any help will greatly appreciated.

 

1 ACCEPTED SOLUTION

@NickProp28 

is this what you want? pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

13 REPLIES 13
Greg_Deckler
Super User
Super User

@NickProp28 You can use LOOKUPVALUE or MAXX(FILTER(...),...) for that.

Excel to DAX Translation - Microsoft Power BI Community


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Dear @Greg_Deckler ,

 

I used LOOKUPVALUE, but result that it copying the result to every single row (based on ID) in my table. 
I only need the result show in only one of the record.
Kindly advice

@NickProp28 So which record would that be? In other words how do you identify which row you want it in? 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Dear @Greg_Deckler ,

 

Any one of the row will be fine. 
If not doing that, at the end if I sum up the value for example C002, I will get 400 instead of 200.

@NickProp28 That's not going to work. This is code. Code is not "hey pick a row, any row", there have to be rules so that the calculation knows when to return a number and when to return blank.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Dear @Greg_Deckler ,

 

How about I create another column to state the sequece number, so the calculation can return to first of the ID?

NickProp28_0-1629722454887.png

 

@NickProp28 Bingo!


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Dear @Greg_Deckler ,

 

Any hits on code could be provided? I lost on the SUM part and how does lookupvalue include the FILTER for num=1 in this case

Thank

@NickProp28 Maybe:

 

Column = 
  VAR __ID = [ID]
RETURN
  IF([Num] <> 1),BLANK(),SUMX(FILTER('Table2',[ID]= __ID),[Weight]))

 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Dear @Greg_Deckler ,

Thanks for the feedback.
Following ur code but I encounter some issues. 

NickProp28_0-1629731515682.png

TABLEA's weight cant be put on this part 

NickProp28_1-1629731568072.png

 

@NickProp28 

is this what you want? pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Dear @ryan_mayu ,

 

Thanks!

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!