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!
Responsive ResidentHi 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.