Hi ! Really appreciate your help in this.. Have been stuck for 3 days..
TableA: 100,000 rows
TableB: 200,000 rows
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]RETURNSELECTCOLUMNS(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?
Go to Solution.
Hi， @learner4eva ；
You could try to create a measure as follows:
The final output is shown below:
Best Regards,Community Support Team_ Yalan WuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
View solution in original post
@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
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 anotherhttps://www.youtube.com/watch?v=Wu1mWxR23jUhttps://www.youtube.com/watch?v=czNHt7UXIe8
Click here to read more about the December 2021 Updates!
Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.
Mark your calendars and join us for our next Power BI Dev Camp!