Virtual Table Rank Optimization

akay23 — Wed, 21 Feb 2024 11:18:52 GMT

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.

Re: Virtual Table Rank Optimization

lbendlin — Wed, 21 Feb 2024 21:53:48 GMT

Merhaba,

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

Re: Virtual Table Rank Optimization

akay23 — Thu, 22 Feb 2024 07:53:29 GMT

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

Re: Virtual Table Rank Optimization

lbendlin — Thu, 22 Feb 2024 12:29:46 GMT

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

topic Re: Virtual Table Rank Optimization in DAX Commands and Tips

Virtual Table Rank Optimization

Re: Virtual Table Rank Optimization

Re: Virtual Table Rank Optimization

Re: Virtual Table Rank Optimization