Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Nanaki
Helper II
Helper II

Identify simultaneous occurrences at locations

Hi,

I have a table that shows events at certain locations. Each location has a different number of machines.
My table shows at which time which machine was occupied. What I want to achieve is a table that shows me which machines at which locations were occupied at the same time.

My Table:

LocationMachineSession Start timeSession End time

1

A01.01.2024 - 12:0001.01.2024 - 13:00
1B01.01.2024 - 14:0001.01.2024 - 14:30
2C14.01.2024 - 12:0014.01.2024 - 13:00
2D14.01.2024 - 12:1514.01.2024 - 12:55
2E14.01.2024 - 12:0014.01.2024 - 12:45
3F14.01.2024 - 10:0014.01.2024 - 11:30
3G14.01.2024 - 10:1414.01.2024 - 10:55
3H14.01.2024 - 10:3014.01.2024 - 10:55
3I14.01.2024 - 10:00

14.01.2024 - 11:00

3F14.01.2024 - 12:0014.01.2024 - 13:30
3G14.01.2024 - 12:1414.01.2024 - 13:55
3H14.01.2024 - 12:3014.01.2024 - 13:55
3I14.01.2024 - 12:00

14.01.2024 - 13:00

 

This table has about 400 locations with at least 2 machines at each location.

Now I would like to get a table that looks something like this:
Where I can see which machines were occupied at the same time in which date range at which location

 

All Machines occupied at the same time
LocationTime-Range
214.01.2024 12:00 - 13:00
314.01.2024 10:00 - 11:00
314.01.2024 12:00 - 13:00

 

And if possible, if I want to take a closer look at a location, a heat map like this one.

 

Last week/month:

Day/Hour123456789101112131415....
Monday2 of 4 / 50%3 of 4 / 75%4 of 4 /100%4 of 4 2 of 43 of 4.................................
Tuesday................................................
Wednesday................................................
....................................................

 

The result didnt have to be exactly like this. 

 

I hope you can help me.

Thanks in advance!

BR
Lennart

1 ACCEPTED SOLUTION
v-jtian-msft
Community Support
Community Support

Hi,@Nanaki 
I am glad to help you !
You are trying to manage multiple machines in multiple directories and you want to see the percentage of machines that are being occupied at various times of the day for the last week.
Here's what I'm trying to do, hopefully it will give you some help.
This is my test data:
This is the initial data, which contains some custom calculated columns and measures.

vjtianmsft_0-1715243828238.png

Below are the measures and calculated columns (partially) created in the table:

Calculated Columns:

 

StartHour = 
 HOUR('use_info'[Session Start time])
EndHour = 
HOUR('use_info'[Session End time])

 

In order to be able to calculate the time of day (24 hours), it is necessary to create a time table Hours

vjtianmsft_1-1715243952436.png

You need to create a corresponding table weekdays with all the weeks in it.

vjtianmsft_2-1715243980265.png

The following steps for implementation:

Create the relationship:

vjtianmsft_3-1715243998503.png

Create measure M_2 to calculate the number of machines that are executing at each time of day

 

M_2 = 
CALCULATE(
    DISTINCTCOUNT('use_info'[Machine]),
    FILTER('use_info',NOT ('use_info'[StartHour]>SELECTEDVALUE(Hours[hourName])||'use_info'[EndHour]<SELECTEDVALUE(Hours[hourName])))
)

 

Then create a measure Machine_count to calculate the total number of machines in each group (grouped by location)

 

Machine_count = CALCULATE(
DISTINCTCOUNT('use_info'[Machine]),FILTER(ALLSELECTED('use_info'),'use_info'[Location]=MAX('use_info'[Location]))
    )

 

vjtianmsft_4-1715244085237.png

Finally create a measure M_result that calculates the percentage of the number of machines running
(here the DIVIDE function is used for division to avoid errors)

 

M_result = DIVIDE([M_2],[Machine_count])

 

vjtianmsft_5-1715244143701.png

The following puts all the parameters that need to be used into a matrix

vjtianmsft_6-1715244172526.png

The weekdayName and hourName fields need to be selected to "Show items with no data"

Add a slicer (you can add a date slicer on top of this to filter the data for the most recent week according to your needs)
Data used by the slicer: (inside the table used is the number of all the locations)

vjtianmsft_7-1715244201594.pngvjtianmsft_8-1715244209807.png

Now you can see the number of machines running in different locations at different times of the day and the percentage of total machines by selecting the slicer's location.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-jtian-msft
Community Support
Community Support

Hi,@Nanaki 
I am glad to help you !
You are trying to manage multiple machines in multiple directories and you want to see the percentage of machines that are being occupied at various times of the day for the last week.
Here's what I'm trying to do, hopefully it will give you some help.
This is my test data:
This is the initial data, which contains some custom calculated columns and measures.

vjtianmsft_0-1715243828238.png

Below are the measures and calculated columns (partially) created in the table:

Calculated Columns:

 

StartHour = 
 HOUR('use_info'[Session Start time])
EndHour = 
HOUR('use_info'[Session End time])

 

In order to be able to calculate the time of day (24 hours), it is necessary to create a time table Hours

vjtianmsft_1-1715243952436.png

You need to create a corresponding table weekdays with all the weeks in it.

vjtianmsft_2-1715243980265.png

The following steps for implementation:

Create the relationship:

vjtianmsft_3-1715243998503.png

Create measure M_2 to calculate the number of machines that are executing at each time of day

 

M_2 = 
CALCULATE(
    DISTINCTCOUNT('use_info'[Machine]),
    FILTER('use_info',NOT ('use_info'[StartHour]>SELECTEDVALUE(Hours[hourName])||'use_info'[EndHour]<SELECTEDVALUE(Hours[hourName])))
)

 

Then create a measure Machine_count to calculate the total number of machines in each group (grouped by location)

 

Machine_count = CALCULATE(
DISTINCTCOUNT('use_info'[Machine]),FILTER(ALLSELECTED('use_info'),'use_info'[Location]=MAX('use_info'[Location]))
    )

 

vjtianmsft_4-1715244085237.png

Finally create a measure M_result that calculates the percentage of the number of machines running
(here the DIVIDE function is used for division to avoid errors)

 

M_result = DIVIDE([M_2],[Machine_count])

 

vjtianmsft_5-1715244143701.png

The following puts all the parameters that need to be used into a matrix

vjtianmsft_6-1715244172526.png

The weekdayName and hourName fields need to be selected to "Show items with no data"

Add a slicer (you can add a date slicer on top of this to filter the data for the most recent week according to your needs)
Data used by the slicer: (inside the table used is the number of all the locations)

vjtianmsft_7-1715244201594.pngvjtianmsft_8-1715244209807.png

Now you can see the number of machines running in different locations at different times of the day and the percentage of total machines by selecting the slicer's location.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.