Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
New table (vlookup) - Based on the ID
Thank for your attention. Any help will greatly appreciated.
Solved! Go to Solution.
is this what you want? pls see the attachment below
Proud to be a Super User!
@NickProp28 You can use LOOKUPVALUE or MAXX(FILTER(...),...) for that.
Excel to DAX Translation - Microsoft Power BI Community
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?
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.
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 Bingo!
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]))
Dear @Greg_Deckler ,
Thanks for the feedback.
Following ur code but I encounter some issues.
TABLEA's weight cant be put on this part
is this what you want? pls see the attachment below
Proud to be a Super User!
you are welcome
Proud to be a Super User!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |