Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
alexa_0028
Resolver II
Resolver II

Performance issue using calculate and SUMX

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?
2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

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

View solution in original post

alexa_0028
Resolver II
Resolver II

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



View solution in original post

4 REPLIES 4
alexa_0028
Resolver II
Resolver II

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
Resolver II
Resolver II

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
Resolver II
Resolver II

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




alexa_0028_0-1632220482499.png

 

amitchandak
Super User
Super User

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors