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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Gsar
Frequent Visitor

Count Specific Time Slots within Time range

Hey Everybody.

 

I am new and have never posted in the forum before, but I have been using it a lot to have some of my questions answered.

 

I have tried to add a sample file for my problem but it seems that I am not able to do it.

 

I have this data set that contains  a a Time Start and a Time End.

 

I want to be able to count the specific half hour time slots that are contained in those time ranges.

 

Table Transactions

Daterandom variableTime StartTime EndWeek id
25-06-2020c108:3010:0011
26-06-2020c108:0017:0012
27-06-2020c209:0012:0023
28-06-2020c209:1512:3024
29-06-2020c110:0011:3015
30-06-2020c313:1516:4036
01-07-2020c312:0017:0047
02-07-2020c410:4013:3548

 Table 2: Time slots

Time StartTime End
08:0008:30
08:3009:00
09:0009:30
09:3010:00
10:0010:30
10:3011:00
11:0011:30
11:3012:00
12:0012:30
12:3013:00
13:0013:30
13:3014:00
14:0014:30
14:3015:00
15:0015:30
15:3016:00
16:0016:30
16:3017:00

 

For example, how many 8-8:30 time slots do I have in my data.

 

What I have done so far and works is cross join the the tables and filter like below.

 

 

Extend = filter(CROSSJOIN('time slots','Transactions'),(Time slots[Time Start] <= 'Transactions'[Time End] && Time slots[Time End] >= 'Transactions'[Time Start]))

 

This allows me to count the time slots but the new extende table is getting too big.

 

I have also tried to count the rows directly in the Time slots table but then I cannot use the calculation for anything else.

 

What I need is a measure that will help me count the rows for the specific time slots. 

 

I want to create a bar chart where the breakdown will be by Time slots.

 

Bascially, I will neeed this measure to countrows the same way that it would do as if I had the cross joined table.

1 ACCEPTED SOLUTION
VahidDM
Super User
Super User

Hi @Gsar 

 

Try this measure:

Count Time Slot = 
VAR _A =
    ADDCOLUMNS(
        'Time slots',
        "C",
            COUNTROWS(
                FILTER(
                    'Table Transactions',
                    'Time slots'[Time Start]  < 'Table Transactions'[Time END]
                       && 'Time slots'[Time End] > 'Table Transactions'[Time Start]
                )
            )
    )
RETURN
    SUMX( _A, [C] )

 

Output:

 

 

VahidDM_1-1638056959205.png

 

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

View solution in original post

4 REPLIES 4
VahidDM
Super User
Super User

Hi @Gsar 

 

Try this measure:

Count Time Slot = 
VAR _A =
    ADDCOLUMNS(
        'Time slots',
        "C",
            COUNTROWS(
                FILTER(
                    'Table Transactions',
                    'Time slots'[Time Start]  < 'Table Transactions'[Time END]
                       && 'Time slots'[Time End] > 'Table Transactions'[Time Start]
                )
            )
    )
RETURN
    SUMX( _A, [C] )

 

Output:

 

 

VahidDM_1-1638056959205.png

 

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

Gsar
Frequent Visitor

I worked on top of your solution and got the expected result. 

I changed the measure to the following

 

Measure = 
VAR _start =
    MAX ( 'Time slots'[Time Start] )
VAR _end =
    MAX ( 'Time slots'[Time End] )
VAR _count =
    COUNTROWS (
        FILTER (
            Transactions,
             _start< Transactions[Time END]
                && _end > Transactions[Time Start]
        )
    )
RETURN
    _count

 

 Now I am getting the following results which looks correct?

Time StartTime EndMeasure
08:00:0008:30:001
08:30:0009:00:002
09:00:0009:30:004
09:30:0010:00:004
10:00:0010:30:004
10:30:0011:00:005
11:00:0011:30:005
11:30:0012:00:004
12:00:0012:30:004
12:30:0013:00:003
13:00:0013:30:004
13:30:0014:00:004
14:00:0014:30:003
14:30:0015:00:003
15:00:0015:30:003
15:30:0016:00:003
16:00:0016:30:003
16:30:0017:00:003
total 3

 

My issue now is in the total.

The total should be 62 but I am getting 3. I should be able to use this measure to count the rows if I drop it to the weeks or the random variable.

For example:

c115
c220
c321
c47
total62

The above is just an example but I hope that you understand the meaning.

 

Please let me know whether I should open another thread for this aditional question. 

 

I would be happy to accept your answer as correct the way it is since you helped immensely to build the one I was looking for. 

smpa01
Super User
Super User

@Gsar  can you please try this measure, pbix is attached

Measure = 
VAR _start =
    MAX ( 'Time slots'[Time Start] )
VAR _end =
    MAX ( 'Time slots'[Time End] )
VAR _count =
    COUNTROWS (
        FILTER (
            Transactions,
            Transactions[Time Start] >= _start
                && _end <= Transactions[Time END]
        )
    )
RETURN
    _count

 

smpa01_0-1637946490050.png

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Gsar
Frequent Visitor

Hey smpa01. 

I am afraid this counting is not what I am looking for. 

 

If you look at id = 2 then the time range is from 8-17, which means that there is only one time slot from 8-8:30.

However, no other id has a starting time from 8 which means that the result for the time slot from 8-8:30 should be 1.

As another example, lets take the time slot from 8:30-9.

In the transaction data, the time slot of 8:30-9 is included on in the id 1,2 so the expected result should be 2.

Please let me know if it makes sense.

 

Best 🙂

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors