cancel
Showing results for
Did you mean:
Frequent Visitor

## Need Help! Creating multiple DAX measures

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?

1 ACCEPTED SOLUTION
Community Support

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.

2 REPLIES 2
Community Support

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.

Super User

@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

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

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Announcements