Forum Discussion
Virtual Table Rank Optimization
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.
3 Replies
- lbendlinSuper User
Merhaba,
this is slow indeed. Can you provide limited sample data that still shows the issue?
- akay23Helper I
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- lbendlinSuper User
Sorry, can't help you without meaningful sample data. I hope someone else can help you further.