Forum Discussion
Measure using different tables and multiple dynamic filters
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 :(
2 Replies
v-yuta-msftCommunity Support
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