Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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.
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |