SUM IF DAX USING TWO TABLES

[Serdet]

Hi All,

I have datasets simailar to the below.

Dataset 1 

ID	Job Type	Estimate Hours
1	Erect	10
2	Dismantle	20

Dataset 2

ID	Task ID
1	1.1
2	2.1

Dataset 3

Task ID	Actual Hours Erect	Actual Hours Dismantle
1.1	11
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

[v-zhangti]

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))

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.

[Serdet]

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:

ID	Job Type	Estimate Hours
1	Erect	10

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

Dataset 1 example 2:

ID	Job Type	Estimate Hours
2	Dismantle	20

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

Hope this makes things clear.