Calculating number of concurrent events on a given day over X amount of rooms with time intervals

[Anonymous]

I'm attempting to calculate room concurrency per day, both by time interval and the max number of rooms used during that time. I've been trying to get this to work, but I can't quite get the result I'm looking for. Below is the general idea of what I'm attempting to recreate in Power BI (this is an example with random numbers/data and not a representative sample of the dataset):

Given a selected date range, calculate the number of days that an occurrence concurrently happened at X time interval in X amount of rooms, Monday-Friday. The time is 15 minute intervals from 7:00AM to 11:00PM.

I'm having trouble figuring out how to get the max number of rooms on the y axis, as well as getting the correct amount of days. My dataset has a date dimension table and the data table where all the events occurred, the names of the rooms, and the start and stop times. I have a separate table for the time intervals (that has the sort and intervals).

I can't use the room names themselves, as I don't need to know the specific room; I just need to know the max number of rooms. I tried making a separate table of just # of Rooms, but it doesn't work as expected--it just spreads the data across all the available points. To add to the complexity, each work location will have a different amount of rooms.

The only thing I have gotten to work so far is counting the intervals:

Interval Count =

CALCULATE (

    DISTINCTCOUNT('Time'[Interval] ),

    FILTER (

        'Time',

        'Time'[Time] >= MAX ( Data[START_TIME] )

            && 'Time'[Time] <= MAX ( Data[STOP_TIME] )

    )

)+0

Interval Result = SUMX(Data,[Interval Count])

Originally I was using this to display room utilization and it worked fine, but I can't figure out how to expand upon this and combine it with the total occurrences on a given day, and then calculate the max number of rooms (the rooms are something like "PBI-Room1"). If I do a distinctcount, it just gives me what you would expect--the total rooms. 

Thanks in advance for any help/guidance on this.

[lbendlin]

That's a standard pattern for a cross join and INTERSECT.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...