Excluding Time Frames in Visual when Populating Values with a Measure and Rows with Time Bins

[villasenorbritt]

I have a measure that is taking seconds, which corresponds with downtime for a specific machine, and turns it into a duration. 

Here is the measure :

Chelsie Eiden's Duration =

// Duration formatting

// * @konstatinos 1/25/2016

// * Given a number of seconds, returns a format of "hh:mm:ss"

//

// We start with a duration in number of seconds

VAR Duration = SUM([actualdowntime])

// There are 3,600 seconds in an hour

VAR Hours = INT ( Duration / 3600)

// There are 60 seconds in a minute

VAR Minutes = INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60)

// Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours

VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number

Var FinalDuration = Hours * 10000 + Minutes * 100 + Seconds

RETURN

// We put the hours, minutes and seconds into the proper "place"

//Hours * 10000 + Minutes * 100 + Seconds

FinalDuration

I'm using TimeBins as well in my visual. So I have the ShiftDate, 60_Min_Bins, and 15_Min_Bins in the Rows section of matrix and then the production and downtime in the values:

I wrote a calculated column I was wanting to use as a filter since break and lunch times should not be counted towards downtime:

Exclude_Time_Period = IF(

(vwDowntime[Time] >= TIME(8,30,0) && vwDowntime[Time] <= TIME(8,45,0)) && vwDowntime[Shift] = "1st Shift" ||

(vwDowntime[Time] >= TIME(11,0,0) && vwDowntime[Time] <= TIME(11,30,0)) && vwDowntime[Shift] = "1st Shift" ||

(vwDowntime[Time] >= TIME(14,0,0) && vwDowntime[Time] <= TIME(14,15,0)) && vwDowntime[Shift] = "1st Shift" ||

(vwDowntime[Time] >= TIME(23,30,0) && vwDowntime[Time] <= TIME(24,00,0)) && vwDowntime[Shift] = "2nd Shift" ||

(vwDowntime[Time] >= TIME(21,00,0) && vwDowntime[Time] <= TIME(21,15,0)) && vwDowntime[Shift] = "2nd Shift" ||

(vwDowntime[Time] >= TIME(2,30,0) && vwDowntime[Time] <= TIME(2,45,0)) && vwDowntime[Shift] = "2nd Shift" ||

(vwDowntime[Time] >= TIME(17,00,0) && vwDowntime[Time] <= TIME(17,30,0)) && vwDowntime[Shift] = "3nd Shift" ||

(vwDowntime[Time] >= TIME(14,00,0) && vwDowntime[Time] <= TIME(14,15,0)) && vwDowntime[Shift] = "3nd Shift" ||

(vwDowntime[Time] >= TIME(20,00,0) && vwDowntime[Time] <= TIME(20,15,0)) && vwDowntime[Shift] = "3nd Shift" ||

(vwDowntime[Time] >= TIME(22,30,0) && vwDowntime[Time] <= TIME(22,45,0)) && vwDowntime[Shift] = "3nd Shift"

,

TRUE(),

FALSE()

)

I tried putting this into the Filters pane and selecting False but the times I'm wanting to exclude still show up. I tried adjusting the Exclusion column to the time bins  and not just my regular time column, and even tried adding it into the duration measure, but nothing is working. Any tips?

[lbendlin]

what's the biggest bin size you can get away with for this?  half hour slots? 15 minute slots? 5 minutes?

[villasenorbritt]

Half hour slots is the biggest bin size I can get away with for this. 

[lbendlin · Accepted solution]

Phew.  Create a calendar table that goes down to that level (ie 48 rows per day) and then based your measures on that new calendar table, using EXCEPT and INTERSECT.