Forum Discussion
Get results from dynamically calculated tables
Hi
I’m having trouble doing this task dynamically in PowerBI.
My data table is a list of flights with information such as flight number, from/to, depart/arrive times.
Flights
| Flight Number | Flight From | Flight To | dep time | arr time |
| 123 | AKL | CHC | 6:00 | 7:25 |
| 456 | BHE | WLG | 8:00 | 8:40 |
| 789 | CHC | AKL | 10:00 | 11:25 |
| 234 | ZQN | AKL | 7:00 | 8:50 |
| 567 | AKL | WLG | 19:00 | 20:10 |
| 891 | WLG | AKL | 16:00 | 17:10 |
| 226 | AKL | CHC | 6:30 | 7:55 |
| 227 | AKL | DUD | 11:45 | 1:30 |
I have two reference tables;
- A list of ports where crew are based and what I want to filter on
- A list of time buckets that I use to group up results
Base
| Base |
| AKL |
| WLG |
| CHC |
| NSN |
| NPL |
| NPE |
| CHC |
Time Bucket
| Time Bucket | Time From | Time To |
| 0000-0100 | 0:00 | 0:59 |
| 0100-0200 | 1:00 | 1:59 |
| 0200-0300 | 2:00 | 2:59 |
| 0300-0400 | 3:00 | 3:59 |
| 0400-0500 | 4:00 | 4:59 |
| 0500-0600 | 5:00 | 5:59 |
| 0600-0700 | 6:00 | 6:59 |
| 0700-0800 | 7:00 | 7:59 |
| 0800-0900 | 8:00 | 8:59 |
| 0900-1000 | 9:00 | 9:59 |
| 1000-1100 | 10:00 | 10:59 |
| 1100-1200 | 11:00 | 11:59 |
| 1200-1300 | 12:00 | 12:59 |
| 1300-1400 | 13:00 | 13:59 |
| 1400-1500 | 14:00 | 14:59 |
| 1500-1600 | 15:00 | 15:59 |
| 1600-1700 | 16:00 | 16:59 |
| 1700-1800 | 17:00 | 17:59 |
| 1800-1900 | 18:00 | 18:59 |
| 1900-2000 | 19:00 | 19:59 |
| 2000-2100 | 20:00 | 20:59 |
| 2100-2200 | 21:00 | 21:59 |
| 2200-2300 | 22:00 | 22:59 |
| 2300-0000 | 23:00 | 23:59 |
Essentially, for any given choice/filter on the base table, I want the flight table filtered to give only flights to or from that location. For flights from that location I want to use the departure time, for flights to that location I want to use the arrival time. From this calculated time I wish to assign which time bucket it falls in to (using Time Bucket table). I need to differentiate from vs to, and then I want to display the # of flights by time bucket and by from/to like below. Example for AKL;
Ultimate Goal
Whats happening in background
| Flight Number | Flight From | Flight To | dep time | arr time | Dep/Arr Time | From/To | Time Bucket |
| 123 | AKL | CHC | 6:00 | 7:25 | 6:00 | From | 0600-0700 |
| 789 | CHC | AKL | 10:00 | 11:25 | 11:25 | To | 1100-1200 |
| 234 | ZQN | AKL | 7:00 | 8:50 | 8:50 | To | 0800-0900 |
| 567 | AKL | WLG | 19:00 | 20:10 | 19:00 | From | 1900-2000 |
| 891 | WLG | AKL | 16:00 | 17:10 | 17:10 | To | 1700-1800 |
| 226 | AKL | CHC | 6:30 | 7:55 | 6:30 | From | 0600-0700 |
| 227 | AKL | DUD | 11:45 | 1:30 | 11:45 | From | 1100-1200 |
The issue is I have done this using CALCULATETABLE to create a table and then graph - but obviously it only works for AKL and isn't dynamic.
What I want to achieve is have the result change dynamically when I change the filter on the base table. So if I filtered the base table for AKL, the above would be the result.
Conversley, if I filtered the base table for CHC, the below would happen in the background
| Flight Number | Flight From | Flight To | dep time | arr time | Dep/Arr Time | From/To | Time Bucket |
| 123 | AKL | CHC | 6:00 | 7:25 | 07:25 | To | 0700-0800 |
| 789 | CHC | AKL | 10:00 | 11:25 | 10:00 | From | 1000-1100 |
| 226 | AKL | CHC | 6:30 | 7:55 | 07:55 | To | 0700-0800 |
Ultimately wanting to display a count per hour bucket and from/to.
I thought the path to go down would be creating 1 or multiple measures that make use of virtual tables calculating using SELECTEDVALUE on the base table, but I just can't get it to work.
Appreciate any help
Cheers
ScottA , Try a measure like this to related arrival time with bucket
calculate(countx(filter(Flights, Flights[arr time] >= Min(TimeBucket[from]) && Flights[arr time] <= max(TimeBucket[to])),[Flight Number]), values(flight[Flight Number]))
You might need to copies time bucket to get this for departure time
3 Replies
Thank you, this pointed me in the right direction. I just made a small amendment to accomplish having it dynamic depending on filter selection of base table
Selected Base = SELECTEDVALUE(Base[Base])Flights From =
VAR SelectedBase = [Selected Base]
VAR FilteredFlightsFrom =
CALCULATETABLE ( Flights, Flights[Flight From] = SelectedBase )
RETURN
CALCULATE (
COUNTX (
FILTER (
FilteredFlightsFrom,
Flights[dep time] >= MIN ( 'Time Bucket'[Time From] )
&& Flights[dep time] <= MAX ( 'Time Bucket'[Time To] )
),
Flights[Flight Number]
),
VALUES ( Flights[Flight Number] )
)ScottA , hope it working for you. Kudos to you.
ScottA , Try a measure like this to related arrival time with bucket
calculate(countx(filter(Flights, Flights[arr time] >= Min(TimeBucket[from]) && Flights[arr time] <= max(TimeBucket[to])),[Flight Number]), values(flight[Flight Number]))
You might need to copies time bucket to get this for departure time
