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, I'm trying to work with a strangely formated table and am having some trouble with creating a calculated column.
I have a table that shows the arrival times for aircraft at an airport (arrivals). I have another table that has the hourly capacity limits for that runway (capacity). What I want to add is a calculated column in the arrivals table that shows what the capacity is for that time.
The problem is the capacity figures are shown in ranges. It has FromDate, ToDate, FromTime, ToTime as columns. Here are some samples of the two tables.
Arrivals
ALDTLeader | Day of the week | ALDTLeaderDateKey | ALDTLeaderTimeKey | Capacity |
2-1-17 6:26 | 2 | 20170102 | 9062634 | |
2-1-17 6:40 | 2 | 20170102 | 9064046 | |
2-1-17 6:43 | 2 | 20170102 | 9064355 | |
2-1-17 6:48 | 2 | 20170102 | 9064804 | |
2-1-17 7:09 | 2 | 20170102 | 9070901 | |
2-1-17 7:11 | 2 | 20170102 | 9071139 | |
2-1-17 7:13 | 2 | 20170102 | 9071306 |
Capacity
fromDateKey | toDateKey | fromTimeKey | toTimeKey | dayOfTheWeek | operationType | capacity |
20170110 | 20170205 | 9000000 | 9045900 | 1 | LLEGADAS | 24 |
20170110 | 20170205 | 9000000 | 9045900 | 2 | LLEGADAS | 24 |
20170110 | 20170205 | 9000000 | 9045900 | 3 | LLEGADAS | 24 |
20170110 | 20170205 | 9000000 | 9045900 | 4 | LLEGADAS | 24 |
20170110 | 20170205 | 9000000 | 9045900 | 5 | LLEGADAS | 24 |
20170110 | 20170205 | 9000000 | 9045900 | 6 | LLEGADAS | 24 |
20170110 | 20170205 | 9000000 | 9045900 | 7 | LLEGADAS | 24 |
20170110 | 20170205 | 9050000 | 9055900 | 1 | LLEGADAS | 18 |
20170110 | 20170205 | 9050000 | 9055900 | 2 | LLEGADAS | 18 |
20170110 | 20170205 | 9050000 | 9055900 | 3 | LLEGADAS | 18 |
20170110 | 20170205 | 9050000 | 9055900 | 4 | LLEGADAS | 18 |
20170110 | 20170205 | 9050000 | 9055900 | 5 | LLEGADAS | 18 |
20170110 | 20170205 | 9050000 | 9055900 | 6 | LLEGADAS | 18 |
The 9s in front of the time keys are a way for the leading zeroes to not get lost, so that is not an error.
Anyway, what I'd like to do is for every row in the arrival table, look at the date and time of arrival (DateKey, TimeKey) and perform a filtered lookup on the capacity table to find the capacity for that ALDT time.
I was thinking somwething along the lines of
Calculated column=CALCULATE(
FILTER(
‘Capacity’(DayOfWeek) = ’arrivals(day of week),
‘Capacity’(fromDateKey) < ‘arrivals(DateKey),
‘Capacity’(toDateKey) > ‘arrivals(DateKey),
‘Capacity’(fromTimeKey) < ‘arrivals(TimeKey),
‘Capacity’(toTimeKey) < ‘arrivals(TimeKey) ),
LOOKUPVALUE(‘Capacity’(capacity))
)
Thanks for any guidance,
Hi @AlanGroskreutz,
I try to figure out a solution for you. But we can not create n:n relationship between two tables. You want to filter ‘Capacity’(DayOfWeek) = ’arrivals(day of week), there should be a relationship based on DayOfWeek, while there is no unique value for two columns of the two table. For example, there are mutiple 2 day of week rows in 'Arrivals' and mutiple 2 day of week rows in 'Capacity', we can not decide which row is used to other filters:
‘Capacity’(fromDateKey) < ‘arrivals(DateKey),
‘Capacity’(toDateKey) > ‘arrivals(DateKey),
‘Capacity’(fromTimeKey) < ‘arrivals(TimeKey),
‘Capacity’(toTimeKey) < ‘arrivals(TimeKey) ),
Best Regards,
Angelia
Thanks, I'l give it a look and let you know if it does the trick.
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |