Create a Crossjoin Table with boolean condition

[akay23]

Hi all,

I have a flight list table that it has 700k rows.

I want to find all possible connections within 12 hours. That's why I tried to crossjoin formula with conditions like;

Filter(
Crossjoin(
 Selectcolumns(
  Data,
   "Carrier1", Data[Carrier Code],
   "Dep1", Data[Dep Airport],
   "Arr1, Data[Arr Airport],
   "ArrTime",Data[Arr Time]),
 Selectcolumns(
  Data,
   "Carrier2", Data[Carrier Code],
   "Dep2", Data[Dep Airport],
   "Arr2, Data[Arr Airport],
   "DepTime",Data[Dep Time])),
[carrier1] = [carrier2] && [Arr1] = [Dep2] && [DepTime] - [ArrTime] < 0.5 &&  0 < [DepTime] - [ArrTime])

But filter uses row context and I think thats why this calculation is so long. My computer can not handle this calculation.

Can it be a calculatetable or boolean solution for this problem? 
I think I have to crossjoin to rows after filtering. How can I do that?

Thank you.
  

[akay23]

Sorry, you are right. This list is my sample data. 

Flight No	Carrier Code	Key	Dep Airport	Arr Airport	Dep Time	Arr Time	Weekday
1	AA	DXBJED	DXB	JED	5:05	6:00	Monday
2	AA	JEDCAI	JED	CAI	8:05	10:30	Monday
3	AA	JEDLHR	JED	LHR	11:05	13:00	Monday
4	AA	JEDDXB	JED	DXB	15:05	17:00	Monday

And, I want this table to find connection time at the hub (Arr Airport1 & Dep Airport2).
I have conditions;
Two Carrier codes must be same,
Arr Airport Code1 and Dep Airport Code2 must be same (Thats why I called hub),
Dep Time2 is bigger than Arr Time1 and the difference between two is smaller than 12 hours.

Like this table;

Flight No1

Carrier Code1

Key1

Dep Airport1

Arr Airport1

Dep Time1

Arr Time1

Weekday1

Flight No1

Carrier Code2

Key2

Dep Airport2

Arr Airport2

Dep Time2

Arr Time2

Weekday

1

AA

DXBJED

DXB

JED

5:05

6:00

Monday

2

AA

JEDCAI

JED

CAI

8:05

10:30

Monday

1

AA

DXBJED

DXB

JED

5:05

6:00

Monday

3

AA

JEDLHR

JED

LHR

11:05

13:00

Monday

1

AA

DXBJED

DXB

JED

5:05

6:00

Monday

4

AA

JEDDXB

JED

DXB

15:05

17:00

Monday

2

AA

JEDCAI

JED

CAI

8:05

10:30

Monday

5

AA

DXBJED

CAI

JED

5:05

6:00

Monday

2

.

.

.

.

.

.

.

.

.

.

.

.

.

.

This is the problem.
My first table has 700k rows. So when I cartesian table with this code,

Filter(
Crossjoin(
 Selectcolumns(
  Data,
   "Carrier1", Data[Carrier Code],
   "Dep1", Data[Dep Airport],
   "Arr1, Data[Arr Airport],
   "ArrTime",Data[Arr Time]),
 Selectcolumns(
  Data,
   "Carrier2", Data[Carrier Code],
   "Dep2", Data[Dep Airport],
   "Arr2, Data[Arr Airport],
   "DepTime",Data[Dep Time])),
[carrier1] = [carrier2] && [Arr1] = [Dep2] && [DepTime] - [ArrTime] < 0.5 &&  0 < [DepTime] - [ArrTime])

This is so slow.
Is there any possibility to rewrite this code using calculatetable or smth. Because if my conditions filtered before crossjoin I think it can be fast.
Thank you 

[vanessafvg]

can you please give some sample data, even if not real data and give what you expect the solution to be?

are you saying you want to know every flight in the sky in within 12 hours of a flight?  

if you are able to provide

1, sample data with expected solution then it is very easy to work it out for you.   This doesn't look like the best to do to this, you are better to model yoru tables more effectively, and do the calcation a very different way, as i am not sure those will work, but first one needs to understand the rule in your data that the data must adhere to, and how you have your model set up.  then it would be easier to figure out how to model this solution  The answer is in how you model your data.

can you provide some sample data or your pbix?