Forum Discussion
Resolver IIFind count of rows between start-end date in same table
Hi there,
I'm having a brainfart for something that i initially thought would be very easy. I hope someone can help me with this.
I have a single table like so:
| ID person | datetime start | datetime planned end | status |
| 1 | 01-10-24 09:00 | 01-10-24 14:00 | completed |
| 2 | 08-11-24 13:00 | 08-11-24 23:00 | completed |
| 3 | 10-10-24 09:00 | 10-10-24 17:00 | cancelled |
| 1 | 02-10-24 10:00 | 02-10-24 14:00 | completed |
| 3 | 10-10-24 10:30 | 10-10-24 14:00 | completed |
What i'm trying to do is find the count of rows for each person and row that either began or ended in the timeframe between the start and end datetime. For example, what i'm looking for is this:
| ID person | datetime start | datetime planned end | status | # between start-end |
| 1 | 01-10-24 09:00 | 01-10-24 14:00 | completed | 0 |
| 2 | 08-11-24 13:00 | 08-11-24 23:00 | completed | 0 |
| 3 | 10-10-24 09:00 | 10-10-24 17:00 | cancelled | 1 |
| 1 | 02-10-24 10:00 | 02-10-24 14:00 | completed | 0 |
| 3 | 10-10-24 10:30 | 10-10-24 14:00 | completed | 0 |
How do i go about this? Many thanks in advance!
Hi JGroothedde ,
Please refers to the following steps.
1. Create two dimension tables for the /datetime start/ field and the /datetime planned end/ fieldDimStartime = VALUES('Table'[datetime start]) DimEndtime = VALUES('Table'[datetime planned end])2. Creates a measure that calculates the count of rows.
Test = VAR _result = IF(SELECTEDVALUE('DimEndtime'[datetime planned end])>SELECTEDVALUE('DimStartime'[datetime start]), CALCULATE( COUNTROWS('Table'), KEEPFILTERS('Table'[datetime planned end]<=SELECTEDVALUE('DimEndtime'[datetime planned end])&&'Table'[datetime start]>=SELECTEDVALUE(DimStartime[datetime start])) ,'Table'[status]="Completed" ) ) RETURN IF(_result<>BLANK(),1,0)
3. Create slicers using fields from both dimension tables.
The result is shown below and hopefully meets your needs.
Please see the attached pbix for reference.
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi JGroothedde ,
Please refers to the following steps.
1. Create two dimension tables for the /datetime start/ field and the /datetime planned end/ fieldDimStartime = VALUES('Table'[datetime start]) DimEndtime = VALUES('Table'[datetime planned end])2. Creates a measure that calculates the count of rows.
Test = VAR _result = IF(SELECTEDVALUE('DimEndtime'[datetime planned end])>SELECTEDVALUE('DimStartime'[datetime start]), CALCULATE( COUNTROWS('Table'), KEEPFILTERS('Table'[datetime planned end]<=SELECTEDVALUE('DimEndtime'[datetime planned end])&&'Table'[datetime start]>=SELECTEDVALUE(DimStartime[datetime start])) ,'Table'[status]="Completed" ) ) RETURN IF(_result<>BLANK(),1,0)
3. Create slicers using fields from both dimension tables.
The result is shown below and hopefully meets your needs.
Please see the attached pbix for reference.
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JGrootheddeThank you Anonymous i have no clue why i didn't think of creating dimtables in the first place. Lifesaver, cheers!
- JGroothedde
I'm trying something like this now. It works, but is extremely slow. Anyone have any tips to speed this up? 🙂
Test = VAR _date = FORMAT(SELECTEDVALUE(Orders[Datetime start]),"dd-MM-yyyy") VAR _starttime = FORMAT(SELECTEDVALUE(Orders[Datetime start]),"HH:mm") VAR _endtime = FORMAT(SELECTEDVALUE(Orders[Datetime planned end]),"HH:mm") VAR _idperson = SELECTEDVALUE(Orders[ID person]) VAR _idorder = SELECTEDVALUE(Orders[ID Order]) RETURN CALCULATE( [Count of orders], ,FILTER( ALL(Orders), Orders[ID Person] = _idperson && Orders[ID Order] <> _idorder && Orders[Date planned] = _date && Orders[Status] = "Completed" && OR( AND( FORMAT(Orders[Datetime start],"HH:mm") >= _starttime ,FORMAT(Orders[Datetime start],"HH:mm") <= _endtime ) , AND( FORMAT(Orders[Datetime planned end],"HH:mm") >= _starttime ,FORMAT(Orders[Datetime planned end],"HH:mm") <= _endtime ) ) ) )