Forum Discussion

Gsar's avatar
Gsar
Frequent Visitor
4 years ago
Solved

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.

  • VahidDM's avatar
    VahidDM
    4 years ago

    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:

     

     

     

     

     

    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/

     

4 Replies

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    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

     

     

     

    • Gsar's avatar
      Gsar
      Frequent Visitor
      4 years ago

      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 🙂

       

  • Gsar's avatar
    Gsar
    Frequent Visitor
    4 years ago

    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. 

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    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:

     

     

     

     

     

    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/