Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have the following data model (2 tables linked by Sensor Number)
'DATA TABLE'
&
'SENSOR ASSOCIATIONS TABLE'
I need to have 4 measures that can calculate;
I have tried lots of ways of doing this but cant crack it. Once i have the logic straight i can append for other things. The data set is millions of sensors, hundreds of rooms over several years. The results are subject to slicers on the report pages.
Thanks
@PaulHallam Please share data in excel using onedrive/google drive to get your answer quickly. Remove any sensitive information before sharing.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@PaulHallam the result you provided in the original post, are we expecing the same result from the dataset you provided?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Yes, its the same sample data. Thanks for looking at this
@PaulHallam how you get to following number what is the logic?
Maximum number of collaborative rooms used in any hour period = (2)
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi,
Apologies, as this data set is small it may be a little less obvious than with a full set.
Looking at the Sensor Associations Table there are two collaborative rooms (Room A and Room B)
The maximum number measure that im trying to get is the maximum number of rooms that were used in any hour period over the dates selected. A room is counted as used if any sensor shows occupancy.
Because Room A and Room B were both occupied in the same hour (at 9:00,11:00 and 13:00 on the 02/01/2019) the maximum concurrent use of rooms is 2.
Hope this makes it clear.
@PaulHallam hmmm i thought the same too but in this case we are getting room count 3, are you ignoring room with "-" dash.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@PaulHallam hang on a sec, i think i just looked at time and get 3 rooms but based on date and time I think I will get 2
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@PaulHallam isn't it following will return 3
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
There are only 2 rooms listed Room A and Room B, anything without a Room Name is not a Room. Perhaps i should have left these blank
@PaulHallam ok make sense, will do.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@PaulHallam I had made the calculation for #1, but not sure about others, could you please put all this in excel sheet sample data dn the calculations, it will help to get this going otehrwise there will be lot of back and forth.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Any luck with these mesures guys?
I have resaved the spreadsheet with the calculations and explanation on,
Thansk for looking at this for me
DATA TABLE
Sensor Number | Date | Time | Occupied |
Sensor 1 | 01/01/2019 | 09:00 | 1 |
Sensor 1 | 01/01/2019 | 10:00 | 0 |
Sensor 1 | 01/01/2019 | 11:00 | 1 |
Sensor 1 | 01/01/2019 | 12:00 | 0 |
Sensor 1 | 01/01/2019 | 13:00 | 1 |
Sensor 1 | 01/01/2019 | 14:00 | 0 |
Sensor 1 | 01/01/2019 | 15:00 | 0 |
Sensor 1 | 01/01/2019 | 16:00 | 0 |
Sensor 2 | 01/01/2019 | 09:00 | 0 |
Sensor 2 | 01/01/2019 | 10:00 | 0 |
Sensor 2 | 01/01/2019 | 11:00 | 0 |
Sensor 2 | 01/01/2019 | 12:00 | 0 |
Sensor 2 | 01/01/2019 | 13:00 | 0 |
Sensor 2 | 01/01/2019 | 14:00 | 0 |
Sensor 2 | 01/01/2019 | 15:00 | 1 |
Sensor 2 | 01/01/2019 | 16:00 | 1 |
Sensor 3 | 01/01/2019 | 09:00 | 0 |
Sensor 3 | 01/01/2019 | 10:00 | 0 |
Sensor 3 | 01/01/2019 | 11:00 | 0 |
Sensor 3 | 01/01/2019 | 12:00 | 0 |
Sensor 3 | 01/01/2019 | 13:00 | 0 |
Sensor 3 | 01/01/2019 | 14:00 | 0 |
Sensor 3 | 01/01/2019 | 15:00 | 0 |
Sensor 3 | 01/01/2019 | 16:00 | 0 |
Sensor 4 | 01/01/2019 | 09:00 | 0 |
Sensor 4 | 01/01/2019 | 10:00 | 0 |
Sensor 4 | 01/01/2019 | 11:00 | 0 |
Sensor 4 | 01/01/2019 | 12:00 | 1 |
Sensor 4 | 01/01/2019 | 13:00 | 1 |
Sensor 4 | 01/01/2019 | 14:00 | 1 |
Sensor 4 | 01/01/2019 | 15:00 | 1 |
Sensor 4 | 01/01/2019 | 16:00 | 1 |
Sensor 5 | 01/01/2019 | 09:00 | 1 |
Sensor 5 | 01/01/2019 | 10:00 | 1 |
Sensor 5 | 01/01/2019 | 11:00 | 1 |
Sensor 5 | 01/01/2019 | 12:00 | 1 |
Sensor 5 | 01/01/2019 | 13:00 | 1 |
Sensor 5 | 01/01/2019 | 14:00 | 1 |
Sensor 5 | 01/01/2019 | 15:00 | 1 |
Sensor 5 | 01/01/2019 | 16:00 | 1 |
Sensor 1 | 02/01/2019 | 09:00 | 1 |
Sensor 1 | 02/01/2019 | 10:00 | 0 |
Sensor 1 | 02/01/2019 | 11:00 | 1 |
Sensor 1 | 02/01/2019 | 12:00 | 0 |
Sensor 1 | 02/01/2019 | 13:00 | 1 |
Sensor 1 | 02/01/2019 | 14:00 | 0 |
Sensor 1 | 02/01/2019 | 15:00 | 0 |
Sensor 1 | 02/01/2019 | 16:00 | 0 |
Sensor 2 | 02/01/2019 | 09:00 | 1 |
Sensor 2 | 02/01/2019 | 10:00 | 0 |
Sensor 2 | 02/01/2019 | 11:00 | 0 |
Sensor 2 | 02/01/2019 | 12:00 | 0 |
Sensor 2 | 02/01/2019 | 13:00 | 0 |
Sensor 2 | 02/01/2019 | 14:00 | 0 |
Sensor 2 | 02/01/2019 | 15:00 | 1 |
Sensor 2 | 02/01/2019 | 16:00 | 1 |
Sensor 3 | 02/01/2019 | 09:00 | 1 |
Sensor 3 | 02/01/2019 | 10:00 | 1 |
Sensor 3 | 02/01/2019 | 11:00 | 1 |
Sensor 3 | 02/01/2019 | 12:00 | 0 |
Sensor 3 | 02/01/2019 | 13:00 | 1 |
Sensor 3 | 02/01/2019 | 14:00 | 1 |
Sensor 3 | 02/01/2019 | 15:00 | 0 |
Sensor 3 | 02/01/2019 | 16:00 | 0 |
Sensor 4 | 02/01/2019 | 09:00 | 1 |
Sensor 4 | 02/01/2019 | 10:00 | 1 |
Sensor 4 | 02/01/2019 | 11:00 | 1 |
Sensor 4 | 02/01/2019 | 12:00 | 1 |
Sensor 4 | 02/01/2019 | 13:00 | 1 |
Sensor 4 | 02/01/2019 | 14:00 | 1 |
Sensor 4 | 02/01/2019 | 15:00 | 1 |
Sensor 4 | 02/01/2019 | 16:00 | 0 |
Sensor 5 | 02/01/2019 | 09:00 | 1 |
Sensor 5 | 02/01/2019 | 10:00 | 1 |
Sensor 5 | 02/01/2019 | 11:00 | 0 |
Sensor 5 | 02/01/2019 | 12:00 | 0 |
Sensor 5 | 02/01/2019 | 13:00 | 0 |
Sensor 5 | 02/01/2019 | 14:00 | 0 |
Sensor 5 | 02/01/2019 | 15:00 | 0 |
Sensor 5 | 02/01/2019 | 16:00 | 1 |
Please see data table Below
DATA TABLE
Sensor Number | Date | Time | Occupied |
Sensor 1 | 01/01/2019 | 09:00 | 1 |
Sensor 1 | 01/01/2019 | 10:00 | 0 |
Sensor 1 | 01/01/2019 | 11:00 | 1 |
Sensor 1 | 01/01/2019 | 12:00 | 0 |
Sensor 1 | 01/01/2019 | 13:00 | 1 |
Sensor 1 | 01/01/2019 | 14:00 | 0 |
Sensor 1 | 01/01/2019 | 15:00 | 0 |
Sensor 1 | 01/01/2019 | 16:00 | 0 |
Sensor 2 | 01/01/2019 | 09:00 | 0 |
Sensor 2 | 01/01/2019 | 10:00 | 0 |
Sensor 2 | 01/01/2019 | 11:00 | 0 |
Sensor 2 | 01/01/2019 | 12:00 | 0 |
Sensor 2 | 01/01/2019 | 13:00 | 0 |
Sensor 2 | 01/01/2019 | 14:00 | 0 |
Sensor 2 | 01/01/2019 | 15:00 | 1 |
Sensor 2 | 01/01/2019 | 16:00 | 1 |
Sensor 3 | 01/01/2019 | 09:00 | 0 |
Sensor 3 | 01/01/2019 | 10:00 | 0 |
Sensor 3 | 01/01/2019 | 11:00 | 0 |
Sensor 3 | 01/01/2019 | 12:00 | 0 |
Sensor 3 | 01/01/2019 | 13:00 | 0 |
Sensor 3 | 01/01/2019 | 14:00 | 0 |
Sensor 3 | 01/01/2019 | 15:00 | 0 |
Sensor 3 | 01/01/2019 | 16:00 | 0 |
Sensor 4 | 01/01/2019 | 09:00 | 0 |
Sensor 4 | 01/01/2019 | 10:00 | 0 |
Sensor 4 | 01/01/2019 | 11:00 | 0 |
Sensor 4 | 01/01/2019 | 12:00 | 1 |
Sensor 4 | 01/01/2019 | 13:00 | 1 |
Sensor 4 | 01/01/2019 | 14:00 | 1 |
Sensor 4 | 01/01/2019 | 15:00 | 1 |
Sensor 4 | 01/01/2019 | 16:00 | 1 |
Sensor 5 | 01/01/2019 | 09:00 | 1 |
Sensor 5 | 01/01/2019 | 10:00 | 1 |
Sensor 5 | 01/01/2019 | 11:00 | 1 |
Sensor 5 | 01/01/2019 | 12:00 | 1 |
Sensor 5 | 01/01/2019 | 13:00 | 1 |
Sensor 5 | 01/01/2019 | 14:00 | 1 |
Sensor 5 | 01/01/2019 | 15:00 | 1 |
Sensor 5 | 01/01/2019 | 16:00 | 1 |
Sensor 1 | 02/01/2019 | 09:00 | 1 |
Sensor 1 | 02/01/2019 | 10:00 | 0 |
Sensor 1 | 02/01/2019 | 11:00 | 1 |
Sensor 1 | 02/01/2019 | 12:00 | 0 |
Sensor 1 | 02/01/2019 | 13:00 | 1 |
Sensor 1 | 02/01/2019 | 14:00 | 0 |
Sensor 1 | 02/01/2019 | 15:00 | 0 |
Sensor 1 | 02/01/2019 | 16:00 | 0 |
Sensor 2 | 02/01/2019 | 09:00 | 1 |
Sensor 2 | 02/01/2019 | 10:00 | 0 |
Sensor 2 | 02/01/2019 | 11:00 | 0 |
Sensor 2 | 02/01/2019 | 12:00 | 0 |
Sensor 2 | 02/01/2019 | 13:00 | 0 |
Sensor 2 | 02/01/2019 | 14:00 | 0 |
Sensor 2 | 02/01/2019 | 15:00 | 1 |
Sensor 2 | 02/01/2019 | 16:00 | 1 |
Sensor 3 | 02/01/2019 | 09:00 | 1 |
Sensor 3 | 02/01/2019 | 10:00 | 1 |
Sensor 3 | 02/01/2019 | 11:00 | 1 |
Sensor 3 | 02/01/2019 | 12:00 | 0 |
Sensor 3 | 02/01/2019 | 13:00 | 1 |
Sensor 3 | 02/01/2019 | 14:00 | 1 |
Sensor 3 | 02/01/2019 | 15:00 | 0 |
Sensor 3 | 02/01/2019 | 16:00 | 0 |
Sensor 4 | 02/01/2019 | 09:00 | 1 |
Sensor 4 | 02/01/2019 | 10:00 | 1 |
Sensor 4 | 02/01/2019 | 11:00 | 1 |
Sensor 4 | 02/01/2019 | 12:00 | 1 |
Sensor 4 | 02/01/2019 | 13:00 | 1 |
Sensor 4 | 02/01/2019 | 14:00 | 1 |
Sensor 4 | 02/01/2019 | 15:00 | 1 |
Sensor 4 | 02/01/2019 | 16:00 | 0 |
Sensor 5 | 02/01/2019 | 09:00 | 1 |
Sensor 5 | 02/01/2019 | 10:00 | 1 |
Sensor 5 | 02/01/2019 | 11:00 | 0 |
Sensor 5 | 02/01/2019 | 12:00 | 0 |
Sensor 5 | 02/01/2019 | 13:00 | 0 |
Sensor 5 | 02/01/2019 | 14:00 | 0 |
Sensor 5 | 02/01/2019 | 15:00 | 0 |
Sensor 5 | 02/01/2019 | 16:00 | 1 |
Thanks
P
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |