How to use multiple tables in single filter in DAX?

[AlanGroskreutz]

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,

[v-huizhn-msft]

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

[Greg_Deckler]

Have a look at my Open Tickets and Periodic Billing Quick Measures in the Quick Measure Gallery. They deal with time ranges.

[AlanGroskreutz]

Thanks, I'l give it a look and let you know if it does the trick.