Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Measures taking a long time - need help to optimize

Greetings PowerBI community, 

This is my first post and I have been struggling with this for a while. I will post a lot of screenshots and try to explain the issue. 
So basically I need help to improve these measures to run faster. I do not understand what is making them so slow. Please help.


I basically have two measures that are running a bit slow according the performance analyzer. These measures are [Coverage weighted] and [Coverage numeric] 

  • Coverage numeric = DIVIDE(fmInvoiceSales[Active Customer Buy],[Active Customer])
  • Coverage weighted = DIVIDE(fmInvoiceSales[KFP SM Weighted Sales],fmInvoiceSales[KFP SM Every Sales])

These measures are simple DIVIDE's however its the 4 measures within these DIVIDES that are heavy. 

 

 

One of the DAX Queries that is taking a long time looks like this. It runs particulary slow when using the slicer "Sales Region" which comes from the dmCustomer table.

 

// DAX Query
DEFINE
VAR __DS0FilterTable = 
TREATAS({"Horeca Sweden",
"North"}, 'dmCustomer'[Sales Region])
 
VAR __DS0FilterTable2 = 
TREATAS({"Senaste 52 veckor"}, 'Period Parameter'[Period])
 
VAR __DS0FilterTable3 = 
TREATAS({TRUE}, 'dmCustomer'[Active Store CRM])
 
VAR __DS0Core = 
SUMMARIZECOLUMNS(
'dmCustomer'[Sales Region],
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
"Coverage_weighted", 'fmInvoiceSales'[Coverage weighted],
"Coverage_numeric", 'fmInvoiceSales'[Coverage numeric]
)
 
VAR __DS0PrimaryWindowed = 
TOPN(1001, __DS0Core, [Coverage_weighted], 0, 'dmCustomer'[Sales Region], 1)
 
EVALUATE
__DS0PrimaryWindowed
 
ORDER BY
[Coverage_weighted] DESC, 'dmCustomer'[Sales Region]

 



DAX Optimization

1 Reply