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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

RANKX to do a TOPN - Lot of Data and Rows - Errors

Good Morning my Friends.

 

Im having some problems and i Believe you guys are able to help!

 

I have a List with over 80.000 Key IDs.

 

Each Key ID represents a Travel.

 

I Wanna do a TOPN List to show just the "more expensives" travels that were made... So I wanna be able to show 25, 50 and 100 differents Travels inside my Dashboard Table.

 

So i tried to follow some steps and do First a RANKX, than a TOPN/TOPNValues Table, and finally a mesuare with a IF to do the LogicalTest between the TOPN Value Selected and my Value's Rank Position to show or not that value.

 

Just like we can see in this video: https://www.youtube.com/watch?v=SsZseKOgrWQ

 

The problem is, How i have more then 80.000 Rows when PowerBI tries to Rank it all in a Dashboard Table its keep Charging and Charging for a very Long times and never ends.... Until my Dashboard table presents an error....

 

Is there Any other option to do this?

 

I believe that is important to say that i wanna Filter my values not just using the TOPN but using others Filters as which Sector has requested the Travel.... So If I Filter by Saying Sector A requested the Travel  I Wanna show the 100s most expansibles Travels that Sector A requested.... If I Filter by Sector B, the same.... and if i Have no Sector Filter I wanna Show the 100s most expansibles Travels made by The Country(Sector is not important in this case)

 

I thought about using an TOPN Function, to Get the first 5000 Values in a new Table and then do the RANKX there, with less Data and less Row(5000 vs 80.000). My fear is that : "What if some of My more Expansibles Travels of one of my Sectors is under those 5000 first Values?" I dont think its the ideal solution.... It most have something better....

 

If the Dashboards Table Filter were Dynamic it would be Perfect.... But how it is not if i use the Dashboards Table Filter i can only choose to Filter by one Constant Number always... And i wanna be able to change for Differents TOPN numbers(Filter by 20, 50, 100, or any other number I Prefer) ... 

1 ACCEPTED SOLUTION

@Anonymous ,

 

Have you tried to publish it ? Check the performance on the service.

Also, can you reduce your model by summarizing or creating dimensions ?



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

8 REPLIES 8
mahoneypat
Employee
Employee

Doing a summary table can certainly help performance if it meet your needs, but Power BI should be able to handle 80,000 rows comfortably.  How complex is your measure?  Can you share it and/or some example data to help troubleshooting?

 

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

Hey Pat,

 

Thanks for the Awnser.

 

Sure, heres a Sample of my data:

 

['2020_Pagamento]

Data.PNG

 

Here is my Measure:

 

RANK.PNG

 

Where [GastoTotal] = Sum('2020_Pagamento'[Valor])

 

Both Sources '2020_Pagamento' and '2020_Viagem' are related by The Travel ID('Identificador do Processo de Viagem')

 

 

And here is the Dashboards Table Charging forever:

 

Charging.PNG

Hi @Anonymous ,

You could try some filters functions in your measure, something like.. rank the values only when the filters are applied.

Also, have you tried this performance on Power BI Service ? I would give it a chance.



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Anonymous
Not applicable

Good Idea!!!

 

 

ll try something like IF TopNValue HASONEVALUE/SELECTEDVALUE.... than RANK it, do you think itll work?

 

Ill try and come back to say if it works!

Anonymous
Not applicable

It did not =/

@Anonymous ,

 

Have you tried to publish it ? Check the performance on the service.

Also, can you reduce your model by summarizing or creating dimensions ?



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Greg_Deckler
Super User
Super User

Are you doing RANKX in a column?

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Nope, I'm Doing RANKX in a Measure

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors