Forum Discussion
Start and end time
Hi there, pretty new to Power BI. I'm trying to replicate something I do in Excel in Power BI, mainly because excel can't handle the amount of data that's being processed.
I'm trying to figure out how full a cafe is in different parts of the day. I have the following data about each customer and what time they arrive and leave the cafe.
| Name | Entry Time | Exit Time |
| Person 1 | 6:00 | 9:00 |
| Person 2 | 7:00 | 8:30 |
| Person 3 | 8:00 | 9:15 |
| Person 4 | 6:45 | 10:00 |
| Person 5 | 7:15 | 7:45 |
And I want to see how many people are actually in the cafe at any one point in time, so like the below
| 6:00 | 6:15 | 6:30 | 6:45 | 7:00 | 7:15 | 7:30 | 7:45 | 8:00 | |
| Person 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| Person 2 | 1 | 1 | 1 | 1 | 1 | ||||
| Person 3 | |||||||||
| Person 4 | 1 | 1 | 1 | 1 | 1 | 1 | |||
| Person 5 | 1 | 1 | 1 | ||||||
| Total | 1 | 1 | 1 | 2 | 3 | 4 | 4 | 4 | 3 |
I can definitely do it in excel but I can't figure out how to get it done in Power BI! Any help would be great!
Hi jojo54
See if the following can fix it for what you need
Number of clients 2 = VAR vMinVal = MIN ( TimeTable[Value] ) VAR vMaxVal = MAX ( TimeTable[Value] ) VAR vNumberOfClients = COUNTROWS ( FILTER ( ALLSELECTED ( ClientsTbl ), ClientsTbl[Entry Time] <= vMinVal && ClientsTbl[Exit Time] >= vMaxVal ) ) RETURN vNumberOfClientsWhenever I have more time I could think of something better.
15 Replies
- mussaenda
Community Champion
Hi jojo54 ,
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PUzBU0lEyszIwAFKWICpWBy5lBBQzh0hZWBmjSBmDxaC6DE2RpUzABpqYAilDAzQTTcEmGkIooJLYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, #"Entry Time" = _t, #"Exit Time" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Entry Time", type time}, {"Exit Time", type time}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Times([Entry Time], 20, #duration(0, 0, 15, 0))), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type time}}), #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each if [Exit Time] < [Custom] then "Hide" else "Show"), #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom.1] = "Show")) in #"Filtered Rows"From the result you posted, The person 3 must also have count in 8:00 time because 8:00 is his entry time, right?
File is here
- wynhopkins
Most Valuable Professional
- mussaenda
Hi wynhopkins,
Thank you fo the compliment. I just used 20 because I assumed that a person stays in a cafe max like 5 hours.
20 can be changed, jojo54 . depends on your need. :)
Have a nice day!
- Geradav
Responsive Resident
Hi jojo54
Here is an alternative using DAX
Number of clients = VAR vMinVal = MIN ( TimeTable[Value] ) VAR vMaxVal = MAX ( TimeTable[Value] ) VAR vClientEntry = CALCULATE ( COUNTROWS ( ClientsTbl ), TimeTable[Value] <= vMinVal, ALL ( TimeTable ) ) VAR vClientExit = CALCULATE ( COUNTROWS ( ClientsTbl ), TimeTable[Value] >= vMaxVal, ALL ( TimeTable ), USERELATIONSHIP ( ClientsTbl[Exit Time], TimeTable[Value] ) ) RETURN MIN ( vClientExit, vClientEntry )Create a time table, then create a relationship between Start Time and the time value, and a second one between Exit Time and the time value. One will be an active relationship, while the second will be inactive.
Like this:
Then you can use that measure in your visualization. Time value in X axis and the measure in the Value area
And you can also make use of the same DAX code in the data model of Excel (Power Pivot)
Let us know what works for you
Thanks for this solution! I like that I can actually write this down to work out what it's doing!
But I'm finding an issue with the below: (updated table)
Name Entry Time Exit Time Person 1 6:00 9:00 Person 2 7:00 8:30 Person 3 8:15 9:15 Person 4 6:45 10:00 Person 5 7:15 7:45 If I were to look at 8am, according to the DAX query,
vcliententry = 4
vclientexit = 4
So query will return 4 being min (4,4)
However there's actually only 3 people who's at the cafe (Person 1,2,4)
I tried writing this on paper and I've worked out that this mis-calculation occurs when there are people on the list who have exited prior to the TimeValue AND there are people who not yet entered into the cafe at the TimeValue.
Any ideas on how to fix this?
Thanks!
- Geradav
Hi jojo54
See if the following can fix it for what you need
Number of clients 2 = VAR vMinVal = MIN ( TimeTable[Value] ) VAR vMaxVal = MAX ( TimeTable[Value] ) VAR vNumberOfClients = COUNTROWS ( FILTER ( ALLSELECTED ( ClientsTbl ), ClientsTbl[Entry Time] <= vMinVal && ClientsTbl[Exit Time] >= vMaxVal ) ) RETURN vNumberOfClientsWhenever I have more time I could think of something better.
Thanks all! Apologies for late reply - going to take a look and give these solutions a go!
