Build Availability Table

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 Participa...

Need Help

DataNinja777

1 year ago

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,

Build Availability Table.pbix65 KB

DataNinja777

Super User

1 year ago

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.