Forum Discussion
Start and end time
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 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!
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
Whenever I have more time I could think of something better.
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?
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 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
