Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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) ...
Solved! Go to 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 ?
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hey Pat,
Thanks for the Awnser.
Sure, heres a Sample of my data:
['2020_Pagamento]
Here is my Measure:
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:
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.
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!
It did not =/
Nope, I'm Doing RANKX in a Measure
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |