Build Availability Table

[stribor45]

Is it possible to build availability table of this data so you can see when there is a time in a week when there are no activities.

Participant	Training	Time	Time	Day 1	Day 2
Participant 1	Driving	7:00:00 PM	20:00:00	M	W
Participant 1	Swimming	8:00:00 AM	9:00:00	M	W
Participant 2	Basketball	6:00:00 PM	20:00:00	Tu	Th
Participant 2	Running	11:00:00 AM	13:00:00	F	F
Participant 3	Basketball	6:00:00 PM	20:00:00	Tu	Th
Participant 3	Running	11:00:00 AM	13:00:00	F	F
Participant 4	Basketball	6:00:00 PM	20:00:00	Tu	Th
Participant 5	Swimming	8:00:00 AM	9:00:00	M	W
Participant 5	Walking	12:00:00 PM	14:00:00	W	F
Participant 6	Driving	7:00:00 PM	20:00:00	M	W
Participant 6	Basketball	6:00:00 PM	20:00:00	Tu	Th
Participant 7	Driving	7:00:00 PM	20:00:00	M	W
Participant 8	Driving	7:00:00 PM	20:00:00	M	W
Participant 9	Walking	12:00:00 PM	14:00:00	W	F

[shafiz_p]

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

[stribor45]

@shafiz_p  can you share the file please if possible. what does the fact table look like. did you change anything?

[shafiz_p]

Same as you provide data. Just changed First Time column to start time and second time column to End time. See fact table image:

Hope this helps!!

[DataNinja777]

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.

 I have attached an example pbix file for your reference.

Best regards,

[stribor45]

I will look at this as well and let you know. thank you

[stribor45]

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. 

[danextian]

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?

[stribor45]

1. Not sure at this point.  Maybe a table where row would be time slows and column with be weekdays. 

2. Beside being misformatted nothing. 6:00:00 PM 20:00:00 should be 6pm to 8pm and so on
3. For the whole table

[danextian]

How would you know which times are vacant if an activity does not indicate its duration?

[stribor45]

I thought you could use second time column? This one would be 3 hours

7:00:00 PM

20:00:00

[danextian]

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?

[stribor45]

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

[danextian]

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.

[stribor45]

@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?

[DataNinja777 · Accepted solution]

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,

[stribor45]

@DataNinja777  would it be possible to adjust this based on additional activity that is attached to training columns? Say for example "driving" training also have "First Aid" activity that is attached to it and may be scheduled always on Wednesdays from 11-12 so every time you counting we leave everything as is only this time we add 1 to Wedesday under column 11

[DataNinja777]

Hi @stribor45 ,

To integrate the "First Aid" schedule as part of the existing timetable, it’s best to handle this transformation in Power Query for consistency and efficiency.

1. Duplicate the Fact Table Query: Start by duplicating the main fact table query.
2. Filter for "Driving" Training: Apply a filter to include only rows where the training is "Driving."
3. Rename Training Field: Rename the "Training" field to "Driving (First Aid)" to clearly indicate that these entries include the "First Aid" component.
4. Adjust Start and End Times: Use the "Replace Values" feature in the Power Query ribbon to set the "Start Time" and "End Time" fields to reflect the "First Aid" schedule (e.g., Wednesdays from 11:00 to 12:00).

The final output will show "Driving (First Aid)" entries with the updated time slots, ensuring a seamless integration of both training and activity in your dataset.

I have attached an example pbix file for your reference.

Best regards,

[stribor45]

@DataNinja777   Thank you for sharing this. "driving" with its additional activity called "first aid" is an exampke  but other actiivties may or may not have additional activity scheduled at different day/time then the main activity.  Would this work as well on this solution?

I was trying to study the example you attached but i noticed that for participant #1 

#1 first aid on wednesday 11 to 12
driving on monday and ewednesday from 7-8 pm
swiming on monday and wednesday from 8:30 to 9:30 am

but the output starts half an hour earlier on  for first aid and driving

[DataNinja777]

Hi @stribor45 ,

Thank you for checking.  I tweaked the formula a little bit to fix the inaccuracy in output.  

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] <= BucketStart &&
            '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
    )

Specifically changed part is as shown below where instead of BucketEnd, BucketStart variable was used.  

Using only BucketStart in the filter keeps the formula straightforward and avoids errors caused by unnecessary checks with BucketEnd. This approach reliably captures participants within each 30-minute interval as intended.

The output is displayed below, and a sample check has confirmed that it now shows the correct results.

I have attached the revised pbix file for your reference.

Best regards,

[stribor45]

I will try this and let you know.