Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Hours Expended Based on Conditions

Hello,

 

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

 

Example Dataset 

IDCrew SizeTime DeltaStart / FinishJob Type (Erect or Dismantle)
131StartErect
132 Erect
133 Erect
134 Erect
135 Erect
136 Erect
137FinishErect
212StartErect
212  
212  

 

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?

 

 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • tamerj1's avatar
    tamerj1
    4 years ago

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

1 Reply

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

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