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
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
Date | random variable | Time Start | Time End | Week | id |
25-06-2020 | c1 | 08:30 | 10:00 | 1 | 1 |
26-06-2020 | c1 | 08:00 | 17:00 | 1 | 2 |
27-06-2020 | c2 | 09:00 | 12:00 | 2 | 3 |
28-06-2020 | c2 | 09:15 | 12:30 | 2 | 4 |
29-06-2020 | c1 | 10:00 | 11:30 | 1 | 5 |
30-06-2020 | c3 | 13:15 | 16:40 | 3 | 6 |
01-07-2020 | c3 | 12:00 | 17:00 | 4 | 7 |
02-07-2020 | c4 | 10:40 | 13:35 | 4 | 8 |
Table 2: Time slots
Time Start | Time End |
08:00 | 08:30 |
08:30 | 09:00 |
09:00 | 09:30 |
09:30 | 10:00 |
10:00 | 10:30 |
10:30 | 11:00 |
11:00 | 11:30 |
11:30 | 12:00 |
12:00 | 12:30 |
12:30 | 13:00 |
13:00 | 13:30 |
13:30 | 14:00 |
14:00 | 14:30 |
14:30 | 15:00 |
15:00 | 15:30 |
15:30 | 16:00 |
16:00 | 16:30 |
16:30 | 17: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.
Solved! Go to Solution.
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/
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/
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 Start | Time End | Measure |
08:00:00 | 08:30:00 | 1 |
08:30:00 | 09:00:00 | 2 |
09:00:00 | 09:30:00 | 4 |
09:30:00 | 10:00:00 | 4 |
10:00:00 | 10:30:00 | 4 |
10:30:00 | 11:00:00 | 5 |
11:00:00 | 11:30:00 | 5 |
11:30:00 | 12:00:00 | 4 |
12:00:00 | 12:30:00 | 4 |
12:30:00 | 13:00:00 | 3 |
13:00:00 | 13:30:00 | 4 |
13:30:00 | 14:00:00 | 4 |
14:00:00 | 14:30:00 | 3 |
14:30:00 | 15:00:00 | 3 |
15:00:00 | 15:30:00 | 3 |
15:30:00 | 16:00:00 | 3 |
16:00:00 | 16:30:00 | 3 |
16:30:00 | 17:00:00 | 3 |
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:
c1 | 15 |
c2 | 20 |
c3 | 21 |
c4 | 7 |
total | 62 |
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.
@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
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 🙂
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
29 | |
14 | |
11 | |
10 | |
9 |