Forum Discussion
Build Availability Table
Hi stribor45 ,
Certainly. You can add a 30 minutes bucket field in the time table:
TimeTable = ADDCOLUMNS( GENERATESERIES(0, 1439, 1), "Time", TIME(INT([Value] / 60), MOD([Value], 60), 0), "Hour", INT([Value] / 60), "Minute", MOD([Value], 60), "Hour-Minute", FORMAT(TIME(INT([Value] / 60), MOD([Value], 60), 0), "hh:mm"), "30-Minute Bucket", FORMAT( TIME(INT([Value] / 60), IF(MOD([Value], 60) < 30, 0, 30), 0), "hh:mm" ) )Then write a measure like below:
Participants during Duration 30Min = VAR SelectedTime = MAX('TimeTable'[Time]) // Assume Time is in "HH:MM" format VAR BucketStart = TIME(HOUR(SelectedTime), IF(MINUTE(SelectedTime) < 30, 0, 30), 0) VAR BucketEnd = IF(MINUTE(SelectedTime) < 30, BucketStart + TIME(0, 30, 0), BucketStart + TIME(1, 0, 0)) // Concatenate participants within the time window VAR ParticipantsList = CONCATENATEX( FILTER( DISTINCT('Table'), 'Table'[Start Time] <= BucketEnd && 'Table'[End Time] >= BucketStart ), RIGHT('Table'[Participant], 1), ", " ) // Return either the list of participants or '〇' if no participants RETURN IF( ISBLANK(ParticipantsList), "-", // Display "-" when no participants ParticipantsList )You can use the measure above alongside a color measure in conditional formatting to achieve a visualization shown at the bottom.
Color = IF ([Participants during Duration 30Min]="-","#90EE90","#FF0000") //the color can be replaced with hexadecimal codes like #6B2328)))I have attached an example pbix file for your reference.
Best regards,
I was under the impression that Time 1 was for Day1 and Time 2 was for Day 2. So the activities occur at the same time regardless fo the day?
Yes so basically this one will be on mondays and wednesdays from 7-8pm
Participant 1 Driving 7:00:00 PM 20:00:00 M W
Can you check on this sample pbix.
So basically I created a table of Day and Time (1 hr interval) and check whether the combination of Day and Start Time or Day and End Time exists in the data table. Anything that doesn't exist is available.
I will try this and let you know.
DataNinja777 would this also work on half an hour starts as well.
What is start and end times are 8:30 to 9:30 istead of 8:00 to 9:00?
Hi stribor45 ,
Certainly. You can add a 30 minutes bucket field in the time table:
TimeTable = ADDCOLUMNS( GENERATESERIES(0, 1439, 1), "Time", TIME(INT([Value] / 60), MOD([Value], 60), 0), "Hour", INT([Value] / 60), "Minute", MOD([Value], 60), "Hour-Minute", FORMAT(TIME(INT([Value] / 60), MOD([Value], 60), 0), "hh:mm"), "30-Minute Bucket", FORMAT( TIME(INT([Value] / 60), IF(MOD([Value], 60) < 30, 0, 30), 0), "hh:mm" ) )Then write a measure like below:
Participants during Duration 30Min = VAR SelectedTime = MAX('TimeTable'[Time]) // Assume Time is in "HH:MM" format VAR BucketStart = TIME(HOUR(SelectedTime), IF(MINUTE(SelectedTime) < 30, 0, 30), 0) VAR BucketEnd = IF(MINUTE(SelectedTime) < 30, BucketStart + TIME(0, 30, 0), BucketStart + TIME(1, 0, 0)) // Concatenate participants within the time window VAR ParticipantsList = CONCATENATEX( FILTER( DISTINCT('Table'), 'Table'[Start Time] <= BucketEnd && 'Table'[End Time] >= BucketStart ), RIGHT('Table'[Participant], 1), ", " ) // Return either the list of participants or '〇' if no participants RETURN IF( ISBLANK(ParticipantsList), "-", // Display "-" when no participants ParticipantsList )You can use the measure above alongside a color measure in conditional formatting to achieve a visualization shown at the bottom.
Color = IF ([Participants during Duration 30Min]="-","#90EE90","#FF0000") //the color can be replaced with hexadecimal codes like #6B2328)))I have attached an example pbix file for your reference.
Best regards,