cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mhdtra
Frequent Visitor

Expanding the a status column value based on the start and end time

Hi, 

I am new to PowerBI, I have a table containing 4 columns equipmentName, equimentStatus (enumeration), equipment in use (binary), and start and endtime (date). the objective is to create a table to track the status of the equipment on an hourly basis per day (the table will have 24 columns representing each hour of the day and the rows represent the list of the equipment, how to populate the table with the status based on the start and end time. the end goal is to obtain a heatmap table to display the status of the equipment hourly during a day based on the events table.  what is the best approach?

Thanks in advance for your support.

1 ACCEPTED SOLUTION

Here's a rather feeble attempt to do this with a standard visual.  Might be better off looking for a custom visual.

 

 

View solution in original post

6 REPLIES 6
lbendlin
Super User
Super User

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Hello,

Below a sample of the status change events table and the desired status chart per machine

Thanks
Sample Data (Assetstatus change event with start and endtime)Sample Data (Assetstatus change event with start and endtime)

 

Daily Status chart per machineDaily Status chart per machine

 

Please provide sample data in usable format.  

NameAssetStatusStartDateTimeEndDateTime
CCA04En panne8/27/2022 10:00:00 AM8/27/2022 12:00:00 PM
CCA04Disponible8/26/2022 12:00:00 AM8/27/2022 10:00:00 AM
CCA04Disponible8/27/2022 12:00:00 PM9/1/2022 12:00:00 AM
CNA6En panne8/27/2022 8:00:00 AM8/27/2022 12:00:00 PM
CNA6Disponible8/26/2022 12:00:00 AM8/27/2022 8:00:00 AM
CNA6Disponible8/27/2022 12:00:00 PM9/1/2022 12:00:00 AM

Here's a rather feeble attempt to do this with a standard visual.  Might be better off looking for a custom visual.

 

 

thanks you lbendlin, this should put me on the right track!

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.