Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
akay23
Helper I
Helper I

Virtual Table Rank Optimization

Hi all,

I have a performance problem in my project.

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

akay23_0-1708513847961.png

And;
Another Table is:

akay23_1-1708513905761.png

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])

 

akay23_2-1708513964114.png

My server timing is like that:

akay23_3-1708514059130.png

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.

3 REPLIES 3
lbendlin
Super User
Super User

Merhaba,

 

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

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.

akay23_0-1708587610436.png

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

akay23_1-1708587714014.png

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

 

akay23_2-1708587812041.png

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

akay23_3-1708587917710.png

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

akay23_4-1708587978062.png

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

akay23_5-1708588035642.png

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

akay23_6-1708588119744.png

And my window:

akay23_7-1708588145388.png

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  

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors