Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
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.
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |