topic Re: RANKX to do a TOPN - Lot of Data and Rows - Errors in DAX Commands and Tips

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

Anonymous — Wed, 06 May 2020 15:34:05 GMT

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

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

Greg_Deckler — Wed, 06 May 2020 15:39:03 GMT

Are you doing RANKX in a column?

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

Anonymous — Wed, 06 May 2020 15:40:08 GMT

Nope, I'm Doing RANKX in a Measure

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

mahoneypat — Thu, 07 May 2020 00:26:46 GMT

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

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

Anonymous — Thu, 07 May 2020 16:58:18 GMT

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:

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

camargos88 — Thu, 07 May 2020 22:13:39 GMT

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.

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

Anonymous — Fri, 08 May 2020 15:31:52 GMT

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!

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

Anonymous — Fri, 08 May 2020 21:03:13 GMT

It did not =/

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

camargos88 — Fri, 08 May 2020 23:34:56 GMT

Anonymous ,

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

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