Forum Discussion
Helper IVSlow problems when calculating Pareto
Hi Rai_BI ,
Pls try the following measure, which has the best performance and takes ablout 90 seconds.
Paretto% - optimized2 = VAR vIndex = ROWNUMBER ( ALL ( 'dProducts'[NAME_PRODUCT],'dProducts'[ID_PRODUCT] ), ORDERBY ( [Sales Amount], DESC ) ) VAR vSales = [Sales Amount] VAR vTempTable = ADDCOLUMNS ( ALL ( 'dProducts'[NAME_PRODUCT],'dProducts'[ID_PRODUCT] ), "Sales", [Sales Amount] ) VAR vSalesTotal = SUMX ( vTempTable, [Sales] ) RETURN SUMX ( WINDOW ( 0, ABS, vIndex, ABS, vTempTable, ORDERBY ( [Sales], DESC ) ), [Sales] ) / vSalesTotalThe other optimised measure takes about ten minutes.
From your original measure that would exceed the memory, first optimising to about 10 minutes, then continuing to optimise to 90 seconds, it feels like the limit of the optimisation has been reached, and if 90 seconds doesn't satisfy the demand, there's nothing I can do about it.
Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !
Thank you~
Helper IV
Super UserHi Rai_BI ,
Pls try the following measure, which has the best performance and takes ablout 90 seconds.
Paretto% - optimized2 =
VAR vIndex =
ROWNUMBER (
ALL ( 'dProducts'[NAME_PRODUCT],'dProducts'[ID_PRODUCT] ),
ORDERBY ( [Sales Amount], DESC )
)
VAR vSales = [Sales Amount]
VAR vTempTable =
ADDCOLUMNS (
ALL ( 'dProducts'[NAME_PRODUCT],'dProducts'[ID_PRODUCT] ),
"Sales", [Sales Amount]
)
VAR vSalesTotal =
SUMX ( vTempTable, [Sales] )
RETURN
SUMX (
WINDOW ( 0, ABS, vIndex, ABS, vTempTable, ORDERBY ( [Sales], DESC ) ),
[Sales]
) / vSalesTotal
The other optimised measure takes about ten minutes.
From your original measure that would exceed the memory, first optimising to about 10 minutes, then continuing to optimise to 90 seconds, it feels like the limit of the optimisation has been reached, and if 90 seconds doesn't satisfy the demand, there's nothing I can do about it.
Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !
Thank you~
- Rai_BI
Thank you, but 10 minutes is too long. I need to found another way that load in maximum 10 seconds
- xifeng_L
The best performance can be up to 90 seconds, not 10 minutes. This is the limit of optimization, if you don't find any other solution, consider giving me the Solution! Thanks.