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,
Hi stribor45 As I can understand that you want to manage and visualize the availability of participants' activities throughout the week. You can create a availability table with time slots as rows and days of the week as columns.
The process involves creating a disconnected time table and a calendar table, then create measure to check if an activity is occurring at specific times and days.
To create a Calendar table, try this code:
CalendarTable =
ADDCOLUMNS (
CALENDAR ( DATE ( 2024, 1, 1 ), DATE ( 2024, 12, 31 ) ),
"Weekday", WEEKDAY ( [Date], 2 ),
"DayNameShort", SWITCH (
WEEKDAY ( [Date], 2 ),
1, "M",
2, "Tu",
3, "W",
4, "Th",
5, "F",
6, "Sa",
7, "Su"
)
)Table look like this:
To create a time table, try this code:
TimeTable =
ADDCOLUMNS (
GENERATESERIES ( TIME ( 0, 0, 0 ), TIME ( 23, 30, 0 ), TIME ( 0, 60, 0 ) ),
"TimeText", FORMAT ( [Value], "hh:mm AM/PM" )
)Table look like this:
Finaly the measure, evaluates available or not. Try below code:
IsActivity =
VAR CurrentTime = SELECTEDVALUE ( TimeTable[Value] )
VAR CurrentDay = SELECTEDVALUE ( 'CalendarTable'[DayNameShort] )
RETURN
IF (
COUNTROWS (
FILTER (
Activities,
( Activities[Day 1] = CurrentDay || Activities[Day 2] = CurrentDay ) &&
CurrentTime >= Activities[Start Time] && CurrentTime <= Activities[End Time]
)
) > 0,
"Not Available",
"Available"
)Final output(s):
Output Version 1:
Output Version 2:
Output Version 3:
All tables are disconnected. So, without place measure in value field it will show error. After place measure, it will work correctly.
Hope this helps!!
If this solved your problem, please accept it as a solution and a kudos!!
Best Regards,
Shahariar Hafiz
