Virtual Table Rank Optimization

[akay23]

Hi all,

I have a performance problem in my project.

I have a 'Bağlantı Tablosu2' table like:

And;
Another Table is:

and this is my limited result with this code:

Deneme2 = 

Var NoktaSec = SELECTEDVALUE(Nokta[Nokta])
Var SecilenDakika = SELECTEDVALUE(Matris5[Zaman])
Var SecilenCr = "TK"
Var BlokSure1 = SELECTEDVALUE('Blok Süre Gidiş'[Blok Süre Gidiş])
Var BlokSure2 = SELECTEDVALUE('Blok Süre Dönüş'[Blok Süre Dönüş])
Var TurnAr = SELECTEDVALUE('Yatı Süresi'[Yatı Süresi])
--------------------------------------------------------------------------------------------

Var a1 = ROW("Cr","TK","FlightNo1","Sanal","Dep",NoktaSec,"Varış",SecilenDakika+bloksure1 + bloksure2 + TurnAr)
                             
Var a2 = GENERATE(a1,
					
                    	FILTER(FILTER('TK Tablosu Gidiş',	
                    					[Varış] < 'TK Tablosu Gidiş'[Kalkış]),
                                        'TK Tablosu Gidiş'[Kalkış]- [varış] < 0.33))            					
                    					               					
Var a3 = ADDCOLUMNS(a2,"DDS",0,"Toplam Uçuş Süresi",[Kalkış]-[Varış]+BlokSure2+[BlokSure])

Var a4 = SUMMARIZE(a3,[Cr],[FlightNo1],[FlightNo2],[Dep],[Hub],[Arr],[Toplam Uçuş Süresi],[DDS])

Var a5 = UNION(a4,FILTER('Bağlantı Tablosu2','Bağlantı Tablosu2'[dep] = NoktaSec))

Var a6 = ADDCOLUMNS(a5,			"Sıra1",RANKX(FILTER(a5,[Arr] = EARLIER([Arr])),[Toplam Uçuş Süresi],,DESC),
								"SıraMax1",COUNTROWS(FILTER(A5,[Arr] = EARLIER([Arr]))),
								"DDSMax1",MAXX(FILTER(a5,[Arr] = EARLIER([Arr])),[DDS]))
Var a7 = SUMMARIZE(FILTER(a6,[FlightNo1] = "Sanal" && [DDSMax1] > 0),
													[Arr],
													[Sıra1],
													[SıraMax1],
													[DDSMax1])
--------------------------------------------------------------------------------------------

Var t1 = ROW("Cr","TK","FlightNo2","Sanal","Arr",NoktaSec,"Kalkış",SecilenDakika)
                             
Var t2 = GENERATE('TK Tablosu Dönüş',FILTER(FILTER(t1,	
                    					[Kalkış] > 'TK Tablosu Dönüş'[Varış]),
                                        [Kalkış] -'TK Tablosu Dönüş'[Varış] < 0.33))            					
                    					               					
Var t3 = ADDCOLUMNS(t2,"DDS",0,"Toplam Uçuş Süresi",[Kalkış]-[Varış]+bloksure1+[bloksure])

Var t4 = SUMMARIZE(t3,[Cr],[FlightNo1],[FlightNo2],[Dep],[Hub],[Arr],[Toplam Uçuş Süresi],[DDS])

Var t5 = UNION(t4,FILTER('Bağlantı Tablosu2','Bağlantı Tablosu2'[arr] = NoktaSec))

Var t6 = ADDCOLUMNS(t5,			"Sıra2",RANKX(FILTER(t5,[Dep] = EARLIER([Dep])),[Toplam Uçuş Süresi],,DESC),
								"SıraMax2",COUNTROWS(FILTER(t5,[Dep] = EARLIER([Dep]))),
								"DDSMax2",MAXX(FILTER(t5,[Dep] = EARLIER([Dep])),[DDS]))
Var t7 = SUMMARIZE(FILTER(t6,[FlightNo2] = "Sanal" && [DDSMax2] > 0),
													[Dep],
													[Sıra2],
													[SıraMax2],
													[DDSMax2])
--------------------------------------------------------------------------------------------


Var s1 = GENERATEALL(a7,FILTER(t7, [Dep] = [Arr]))
Var s2 = GENERATEALL(t7,FILTER(a7, [Dep] = [Arr]))
Var s3 = SELECTCOLUMNS(s2,
							"Arr",[Arr],
							"Sıra1",[Sıra1],
							"SıraMax1",[SıraMax1],
							"DDSMax1",[DDSMax1],
							"Dep",[Dep],
							"Sıra2",[Sıra2],
							"SıraMax2",[SıraMax2],
							"DDSMax2",[DDSMax2])
							
						



Var s4 = UNION(s1,s3)
Var s5 = DISTINCT(s4)
				
Var s6 = ADDCOLUMNS(s5,"Değer",((([Sıra1]/[SıraMax1])*[DDSMax2]) + (([Sıra2]/[SıraMax2])*[DDSMax1]) ))
Return
SUMX(s6,[Değer])

My server timing is like that:

I want to calculate more than this. Do you have a suggestion?

My goal is, 
I have a connecting flights table 'Bağlantı Tablosu2'
I want to create a virtual table for every 5 minutes in a week and rank with all conecting flights table aka 'bağlantı tablosu2'.
But this is very slow.

[lbendlin]

Merhaba,

this is slow indeed.  Can you provide limited sample data that still shows the issue?  

[akay23]

Sample data does not show the issue so I share all database's photo.
Bağlantı Tablosu2 (Connection Table): Represent flight data Cr: Carrier Code, FlightNumbers, Toplam Uçuş Süresi means total flight duration, DDS means Departure-Arrival passenger data.

TK Tablosu Gidiş: Data shows that second leg of our virtual flight. Virtual flight is first leg in that case

TK Tablosu Dönüş: Data shows that first leg of our virtual flight.Virtual flight is second leg in that case

This is matrix table that every 5 minutes of the week.

Blok Süre Gidiş: is the duration of our virtual flight first way

Blok Süre Dönüş: is the duration of our virtual flight second way

Yatı süresi: Means waiting duration of between first and second flight

And my window:

My main goal is I select an airport and Duration of first and second leg flight. With this for every 5 minutes of departing and for 5 minutes of waiting, I want to calculate rank of my virtual flight.

Thank you  

[lbendlin]

Sorry, can't help you without meaningful sample data. I hope someone else can help you further.