Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello PowerBI community, could you please help me with the following question?
I have these two tables that I want to filter using the Service Hour that I choose, for example, I'm interessed in the service hours between 7h and 9h. Then, I need to calculate how many passengers were transported divided by vehicle hours, considering only the hours I filtered.
These are the tables :
Shift ID | Start | End | Shift total hours | Service Hours |
1 | 06:00 | 12:00 | 06:00 | 02:00 |
2 | 06:30 | 09:00 | 02:30 | 02:00 |
3 | 06:00 | 08:30 | 02:30 | 01:30 |
4 | 07:00 | 12:00 | 05:00 | 02:00 |
5 | 07:00 | 11:00 | 04:00 | 02:00 |
6 | 08:00 | 10:00 | 02:00 | 01:00 |
Booking ID | Pax | Pickup time | Shift ID | Service pax |
1 | 1 | 06:00 | 1 | 0 |
2 | 1 | 07:00 | 2 | 1 |
3 | 2 | 07:30 | 2 | 2 |
4 | 2 | 08:30 | 3 | 2 |
5 | 5 | 08:45 | 3 | 5 |
6 | 1 | 09:00 | 4 | 1 |
7 | 2 | 09:01 | 4 | 0 |
8 | 1 | 10:00 | 4 | 0 |
So I created the calculated columns Service Hours and Service pax, to calculate the hours each vehicle worked and how many passengers travelled in the hours I want to filter.
But the thing is that I don't know how to do that using dynamic filters, where the user can choose the hours he wants to filter and that will change the calculations and give a new result. Can someone help me with that please?
So in this case I would have 11 passengers divided by 10,5 vehicle hours, so 1,047619 as a result.
I know that I can't use HASONEVALUE in calculated columns but any ideas on how to do that? I'm stucked at the moment 😞
Hi lucas_pbi,
To achieve dynamic filter as you mentioned, you should use measure instead of calculate column like pattern below:
Amount = CALCULATE ( DISTINCTCOUNT ( Table[Shift ID] ), ALLSELECTED ( Table[Services hours] ) )
Regards,
Jimmy Tao
Hello,
With this measure I can count the different shifts but how can I calculate how many hours for each shift between 7h and 9h for example?
Thanks,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |