Build Availability Table

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

Hi stribor45 

Questions:

• What output do you expect from this sample data?
• What is the difference between the two time column other  than having different formats  (12 vs 24 hr format?)?
• No activities for the whole table or by participant?

Would it be easier to count how many participant are starting at same slots

On Mondays  8am 2 participants

On Mondays 7pm 4 participants

...

Say if i wanted to cancel a class and set that same class for some other time slots how many participants would be affected. 

Hi stribor45,

In order to solve your problem, I have created a data model like below:

Your fact able has a relationship with the weekday table.  On the other hand, the time table, generated from the following dax table function remains a disconnected table from your fact 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")
)

As you mentioned that the two weekday columns represent separate dates rather than a duration, I have unpivoted them to create a single weekday column. This column is now connected to the weekday dimension table, as shown above.

The duration of participants' activity during the week can be visualized using the DAX measure below:

Activity Duration = 
VAR SelectedTime = MAX('TimeTable'[Time]) // Assume Time is in a format like "HH:MM"

RETURN
    SUMX(
        'Table',
        IF(
            'Table'[Start Time] <= SelectedTime
                &&  'Table'[End Time] >= SelectedTime,
            // Calculate the duration using max and min datetime in minutes
     1,blank()
        )
    )

 From the above, you can visualize the availability schedule, indicating time slots where everyone can attend—shown by the absence of any highlighting.