Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

How to use multiple tables in single filter in DAX?

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

ALDTLeaderDay of the weekALDTLeaderDateKeyALDTLeaderTimeKeyCapacity
2-1-17 6:262201701029062634 
2-1-17 6:402201701029064046 
2-1-17 6:432201701029064355 
2-1-17 6:482201701029064804 
2-1-17 7:092201701029070901 
2-1-17 7:112201701029071139 
2-1-17 7:132201701029071306 

 

Capacity

fromDateKeytoDateKeyfromTimeKeytoTimeKeydayOfTheWeekoperationTypecapacity
2017011020170205900000090459001LLEGADAS24
2017011020170205900000090459002LLEGADAS24
2017011020170205900000090459003LLEGADAS24
2017011020170205900000090459004LLEGADAS24
2017011020170205900000090459005LLEGADAS24
2017011020170205900000090459006LLEGADAS24
2017011020170205900000090459007LLEGADAS24
2017011020170205905000090559001LLEGADAS18
2017011020170205905000090559002LLEGADAS18
2017011020170205905000090559003LLEGADAS18
2017011020170205905000090559004LLEGADAS18
2017011020170205905000090559005LLEGADAS18
2017011020170205905000090559006LLEGADAS18

 

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,

Need Help

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago
    Have a look at my Open Tickets and Periodic Billing Quick Measures in the Quick Measure Gallery. They deal with time ranges.
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

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

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Anonymous,

    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