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
paulomartins
Frequent Visitor

Measure to sum a column based on other tables column

Hello guys, it's my first post here and I'm very glad to be part of this forum.

 

Recently I'm trying to deal with a logical sum in DAX and to be honest I not dealing with it really well...

 

My problem is:

 

I have 3 tables:

 

paulomartins_0-1657066142563.png

 

paulomartins_1-1657066191404.png

 

paulomartins_2-1657066220496.png

 

I want to sum the MB52[Quantity] based on Current and Old Keys, reaching this result:

 

paulomartins_3-1657066242560.png

 

Identifying [Current Key] and suming the quantity of the [Old. Key] with owns [Current Key] quantity.

 

I tried this code but with no sucess 😞

 

[Sum. Quantity] =
IF
(

'KEYS'[Old. Key] <> "";

CALCULATE(SUM(MB52[Quantity]) ; MAX(MB52[Key]) = MAX('KEYS'[Old. Key])) + CALCULATE(SUM(MB52[Quantity]) ; MAX(MB52[Key]) = MAX('KEYS'[Current Key]));

SUM(MB52[Quantity])

)

 

I apreciate a lot your help and thanks anyway 🙂

1 ACCEPTED SOLUTION
vapid128
Solution Specialist
Solution Specialist

in MB52 table

add colnum

current key

var _newKey=lookupvalue(keys[current key],keys[old key],MB52[KEY])

return

if(_newKey = blank(), MB52[KEY],_newKey)

 

then make relationhip MB52[current key] and product[current key]

View solution in original post

2 REPLIES 2
paulomartins
Frequent Visitor

It's not the expected way I was thinking to reach it, but it worked like a charm hahahaha

 

Thanks so much! 

vapid128
Solution Specialist
Solution Specialist

in MB52 table

add colnum

current key

var _newKey=lookupvalue(keys[current key],keys[old key],MB52[KEY])

return

if(_newKey = blank(), MB52[KEY],_newKey)

 

then make relationhip MB52[current key] and product[current key]

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.

Top Solution Authors