Performance issue using calculate and SUMX

[alexa_0028]

Hi All,

I am having a table visual and I am seeing slow performance while adding the measure "test_acc" and it seems to load very slow.

test_acc =

var test_1 = calculate('Sales'[Volumes], left('Product'[Code],3)="ACC")

RETURN

calculate(SUMX('Sales', test_1 ),'Calendar'[cytd_flag]=1)

Please let me know how can i improve my formula if any great suggestions?

[amitchandak · Accepted solution]

@alexa_0028 , try like

test_acc =
var test_1 = calculate(sum('Sales'[Volumes]), left('Product'[Code],3)="ACC")
RETURN
calculate( test_1,'Calendar'[cytd_flag]=1)

or

test_acc =
var test_1 = calculate(sum('Sales'[Volumes]), filter('Product', left('Product'[Code],3)="ACC"))
RETURN
calculate( test_1,filter('Calendar','Calendar'[cytd_flag]=1))

[alexa_0028 · Accepted solution]

Hi All,

With some retrying, I figured out the way to optimise. The formula I pasted above indeed worked, I was looking the performance along with old measures therefore it was showing high values.
Thank you so much @amitchandak  for pointing me to right direction.

test_acc =

var test_1 = calculate('Sales'[Volumes],KEEPFILTER( left('Product'[Code],3)="ACC") ,KEEPFILTER('Calendar'[cytd_flag]=1))

RETURN

test_1

[alexa_0028]

I tried to rewrite my measure like below , it didn't improve performance much but gave right result:

test_acc =

var test_1 = calculate('Sales'[Volumes], left('Product'[Code],3)="ACC" ,'Calendar'[cytd_flag]=1)

RETURN

test_1

Please help me to optimize the DASX ?

[alexa_0028]

Hi @amitchandak ,

Thanks for your reply, it did improved performance a lil more.
But this solution doesn't give me the right results. I am looking for the results like highlighted in green but I am getting the value of the volume measure only in test_acc

[amitchandak · Accepted solution]

@alexa_0028 , try like

test_acc =
var test_1 = calculate(sum('Sales'[Volumes]), left('Product'[Code],3)="ACC")
RETURN
calculate( test_1,'Calendar'[cytd_flag]=1)

or

test_acc =
var test_1 = calculate(sum('Sales'[Volumes]), filter('Product', left('Product'[Code],3)="ACC"))
RETURN
calculate( test_1,filter('Calendar','Calendar'[cytd_flag]=1))