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
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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!




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