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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jojo54
Frequent Visitor

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.

 

NameEntry TimeExit Time
Person 16:009:00
Person 27:008:30
Person 38:009:15
Person 46:4510:00
Person 57:157: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:006:156:306:457:007:157:307:458: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!

1 ACCEPTED SOLUTION
Geradav
Responsive Resident
Responsive Resident

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
    vNumberOfClients

Annotation 2019-08-09 120930.jpg

 

Whenever I have more time I could think of something better.

View solution in original post

15 REPLIES 15
jojo54
Frequent Visitor

Thanks all! Apologies for late reply - going to take a look and give these solutions a go!

Geradav
Responsive Resident
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:

Annotation 2019-08-03 141705.jpg

 

Then you can use that measure in your visualization. Time value in X axis and the measure in the Value area

Annotation 2019-08-03 142013.jpg

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

@Geradav 

 

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)

NameEntry TimeExit Time
Person 16:009:00
Person 27:008:30
Person 38:159:15
Person 46:4510:00
Person 57:157: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
Responsive Resident
Responsive Resident

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
    vNumberOfClients

Annotation 2019-08-09 120930.jpg

 

Whenever I have more time I could think of something better.

Hi Geradev

 

I have a similar problem to that faced by the OP, and I have tried implementing your solutions for my own purpose, but I am coming up with incorrect results (probably similar to the OP)

I dont yet dabble in BI, but have recently started to teach myself Power Query & Power Pivot in Excel, which is similar, so hopefully not the reason for the results error.

If I create a Pivot Table measure similar to your 1st solution, some results are incorrect for a few periods and a few companies (I need to count number of people from a particular company at each period).
And if I try the 2nd solution the I get the correct Grant Total of people in each period, but it repeats for every company.

 

With regards the 1st solution, it seems that it is trying to relicate an Excel CountIfs function, where it takes the Minimum (by Company) of "People Entering Before the Time Period" & "People Leaving After the Time Period", irrespective of when they entered.

The result I think I actually need is the Count of People who have Entered and Not Left.

Is there a way of doing this?

 

With regards to the 2nd solution, I just havent learnt enough yet to work out how to adapt the formula so it returns by Company.

 

Any help you can give will be gratefully received.

 

Kris

Hi Geradav, seems your solution is based on one date. If there are multiple days, may I ask how to handle it? Thanks! 

Hi @samsaralamb ,

If I understood you correctly, instead of using a time table (with only time values) you could have a table that uses date and time values, so you can obtain frequence over multiple days

hi there, I'm now trying to add an extra column of information which shows which company the person is from.  However when I try to apply a page filter to filter for individual companies, the command seems to still be looking through the whole table and not filtering the count only for that company. Any ideas how to fix it?

@Geradav thank you!! That works perfectly!

Hi @Geradav ,

 

Nice approach using dax! Is there a way to make the interval evey 15mins instead of every 5mins?

Geradav
Responsive Resident
Responsive Resident

Hi @mussaenda 

 

I used a table function to generate a time series with an increment of 5 minutes, so yes, it can easily be changed to 15 minutes.

TimeTable =
GENERATESERIES ( TIME ( 5, 0, 0 ), TIME ( 13, 0, 0 ), TIME ( 0, 15, 0 ) )
mussaenda
Super User
Super User

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?

2019_08_03_10_58_24_Time_Duration_Power_BI_Desktop.png

File is here

Nice approach @mussaenda ,   why use the 20 in List.Times?

 

Also, @jojo54 ,  given this code will work in Excel I'd be interested to know what performance issues your having that you're trying PowerBI.   Is it the 32 Bit Excel version that's limiting you?

 

Wyn

 

I have ~3m lines of data so excel is extremely limiting! I'm looking at it across every day, across a few years

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.