Hours Expended Based on Conditions

[Anonymous]

Hello,

I want to calculated hours expended in my dataset based on a few conditions.

Example Dataset 

ID	Crew Size	Time Delta	Start / Finish	Job Type (Erect or Dismantle)
1	3	1	Start	Erect
1	3	2		Erect
1	3	3		Erect
1	3	4		Erect
1	3	5		Erect
1	3	6		Erect
1	3	7	Finish	Erect
2	1	2	Start	Erect
2	1	2
2	1	2

The above dataset shows a task ID (1), the amount of people working on that job (3), the time spent on each entry, the entry the job was started and finished and what type of job it is (erect).

I want to calculate the time expended on each ID ('crew size' * 'Time Delta') based on the below conditions.

• Has a "start" and a "finish" entry
• Is an "erect" 'Job Type'

Looking at the above dataset the measure would return 84 for Job ID 1. It would return nothing for Job ID 2 as it does not contain a "finish" entry.

How can this be achieved?

[tamerj1 · Accepted solution]

Hi @Anonymous 
Here is a sample file with the solution https://www.dropbox.com/t/wsVgfD6l6EA1AjId

Time Expended = 
SUMX (
    VALUES ( Data[ID] ), 
    CALCULATE (
        VAR StartFinish1 = VALUES ( Data[Start / Finish] )
        VAR StartFinish2 = { "Start", "Finish" }
        VAR TotalTime = SUM ( Data[Time Delta] )
        VAR CrewSize = SELECTEDVALUE ( Data[Crew Size] )
        RETURN 
            IF ( 
                COUNTROWS ( INTERSECT ( StartFinish1, StartFinish2 ) ) = 2,
                CrewSize * TotalTime
            )
    )
)