Need Help! Creating multiple DAX measures

[learner4eva]

Hi ! Really appreciate your help in this.. Have been stuck for 3 days.. 

TableA: 100,000 rows

Version	PartNo	RefNo	Type	PartQty
V1	A123	3532-421	X	5
V1	B234	5754-643	Y	3
V2	C342	4362-346	X	2
V2	A352	6853-435	Z	6

TableB: 200,000 rows

SupportingPartNo	RefNo	Period	RefQty
P004	3532-421	M1	100
P005	6853-435	M1	200
P004	6853-435	M2	50
P006	4362-346	M3	400

I would like to create a measure UseQty.

UseQty = RefQty * PartQty when RefNo (in Table B)= RefNo (in Table A)

Eventually I would like to create charts using Period (Table B), PartNo (Table A), Type(Table A) and UseQty (Measure) for a given Version.

I tried creating an additional column in Table B via PowerQuery but that ended up making the calculation very slow as I have 100k rows in Table A and 200k rows in Table B. 

I tried creating a table measure and I ended up having 3Million rows

UseQty = GENERATEALL(
TableA,
var Ref = A[RefNo]
RETURN
SELECTCOLUMNS(CALCULATETABLE(TableB, TableB[RefNo]=Ref),"PartNo",TableB[SupportingPartNo],"AttachRate",TableB[RefQty], "RequiredQty", TableB[RefQty]*TableA[PartQty])
)

Is there a way to create measures instead of tables?

[v-yalanwu-msft · Accepted solution]

Hi， @learner4eva ；

You could try to create a measure  as follows:

Measure = 
CALCULATE( MAX('TableA'[PartQty]),FILTER('TableA',[RefNo]=MAX('TableB'[RefNo])))*MAX('TableB'[RefQty])

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@learner4eva , You need to create a common dimension refno

RefNo = distinct(Union(all(Table[RefNo]) ,all(Table[RefNo]) )) //or use distinct in place of all

example

Bridge Table: https://www.youtube.com/watch?v=Bkf35Roman8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=19

Then create a measure like

sumx(Values(RefNo[RefNo]) , calculate( sum(RefQty]) * sum(PartQty) ) )

if you want a new column way

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8