Cumulative total very slow

[Anonymous]

I have a measure very slow.

This table 'VendaProduto' contains  4 milions of records. I have tested selecting a single day and a single category product (4000 records) .

With this selection ,in the power Bi desktop about 60 seconds for run this. 

In power bi service i get the error:

Mais detalhesResource Governing: The memory used by the query exceeded the configured limit. The query or calculations referenced by it might be too memory intensive. Please consider simplifying the query or calculations. If the dataset is hosted on a dedicated capacity/server, you may also reach out to your capacity/server administrator to see if the per-query memory limit can be increased. Additional information: Requested 1048579KB, Limit 1048576KB.

Some idea how to improve performance for this.?

Thankyou for all.

[mahoneypat]

Two suggestions/questions:

1.  Do you need both of the ALLSELECTED() functions?  Does your visual have filters on columns from both tables?

2. You are using the whole VendaProduto table in your ALLSELECTED().  In your visual you have a single column.  Does it still work if you use ALLSELECTED(VendaProduto[Product_Descricao])?  Working on only the column you need may improve performance.

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

For such tasks paginated reports should be created. Who's going to read 4000 rows in a visual? PBI is for summarizing data to a relatively high level, not to display 4000 records in one table. Please create paginated reports for such tasks.

By the way, ranking 4000 or more products using - as I suspect - RANKX will ALWAYS be slow. RANKX is slow internally as it has to materialize a huge set of data in memory and do many other things. Your measure is calculated for 4000 products, so at least 4000 context transitions must take place. No wonder it's slow. Of course, you could always add more CPU power and RAM to your system...

Best
D

[Anonymous]

Paginated report dont work for me, because i no have a pro licence.

I'm testing some expressions with better performance.

If i get sucess i will post this here