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
Serdet
Post Patron
Post Patron

SUM IF DAX USING TWO TABLES

Hi All,

 

I have datasets simailar to the below.

 

Dataset 1 

IDJob TypeEstimate Hours
1Erect10
2Dismantle20

 

Dataset 2

IDTask ID
11.1
22.1

 

Dataset 3

Task IDActual Hours ErectActual Hours Dismantle
1.111 
2.1  

 

Dataset 2 & 3 have a relatioship on column 'Task ID'.

 

I want to create a measure that will SUM column 'Estimate Hours' in dataset 1

but only

if column 'Actual Hours' in Dataset 3 is >0 and relevant to the ID & Job Type.

 

For example, using the above logic the measure would return 10 hours.
This is because 'ID' "1" with a 'Job Type' "Erect" appears in dataset 2 & 3 under column 'Actual Erect Hours'.

The 20 hours in for ID 2 in dataset 1 would not be counted as 'ID' "2" with 'Job Type' "Dismantle" does not have any "Dismantle "Hours" in dataset 2 & 3.

 

Hope this makes sense.

 

Many thanks

2 REPLIES 2
v-zhangti
Community Support
Community Support

Hi, @Serdet 

 

You can try the following methods.

Measure = 
Var _table=CALCULATETABLE(VALUES('Dataset 2'[ID]),FILTER(ALL('Dataset 3'),[Actual Hours Erect]>0))
Return
CALCULATE(SUM('Dataset 1'[Estimate Hours]),FILTER(ALL('Dataset 1'),[ID] in _table))

vzhangti_0-1673922826095.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-zhangti , 

Thank you for your response. The above measure will only sum values based on having 'Actual Erect hours'.

 

Dataset 1 will be the key for determing what hours to look at.

 

Dataset 1 example 2:

IDJob Type Estimate Hours
1Erect10

This should only sum the estimate hours if dataset 2 & 3 have >0 'Actual Hours Erect' for 'ID' "1".

 

Dataset 1 example 2:

IDJob TypeEstimate Hours
2Dismantle20

This should only sum the estimate hours if dataset 2 & 3 have >0 'Actual Hours Dismantle' for 'ID' "2".

 

Hope this makes things clear. 

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.