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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
cworkman2015
Helper II
Helper II

Performance Tune Summarize function to GroupBy Function with Filters

Hello,

 

I'm running a summarize function that is wrecking my performance - I read that Group By can give me an advantage in this regard but I'm unsure how to use both Group By and Filter.


here is my current code: 

	var last_date = lastdate(CONFORM_MOVEMENT[BusinessDate])
	VAR max_on_hand_calc = SUMMARIZE(CONFORM_MOVEMENT,CONFORM_MOVEMENT[Store], CONFORM_MOVEMENT[ItemNumber],"max_on_hand",
							CALCULATE(Max(CONFORM_MOVEMENT[TotalonHand]),
								FILTER(ALL(CONFORM_MOVEMENT[BusinessDate]),
									CONFORM_MOVEMENT[BusinessDate] > last_date - max(CONFORM_MOVEMENT[ideal_doh]) &&
									CONFORM_MOVEMENT[BusinessDate] < last_date)))

 

Any suggestions on what could be limiting the performance of measure and how to go about converting to Group By over Summarize?

 

 

Thank you,
Channing

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@cworkman2015 Well, I doubt that it is the SUMMARIZE technically that is wrecking your performance versus the calculation you are doing for the column you are adding as part of your summarize. You are removing all the context with ALL and thus returning all rows and then you are trying to grap rows between two values. That generally speaks trouble. I am wondering why you are using an ALL there for one. What should be in context is all rows for that store and itemnumber anyway. 

 

With GROUPBY, you have to do calculations over CURRENTGROUP() which generally means you are using the X aggregation functions, MAXX, SUMX, etc. If you try to MAXX(FILTER(CURRENTGROUP()... you will get an error.

 

Sample data and expected output might shed some light on a better way of doing things.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

The code, when thrown at DAX Formatter by SQLBI, ends in an error. Please double-check the correctness of your measure.

Greg_Deckler
Super User
Super User

@cworkman2015 Well, I doubt that it is the SUMMARIZE technically that is wrecking your performance versus the calculation you are doing for the column you are adding as part of your summarize. You are removing all the context with ALL and thus returning all rows and then you are trying to grap rows between two values. That generally speaks trouble. I am wondering why you are using an ALL there for one. What should be in context is all rows for that store and itemnumber anyway. 

 

With GROUPBY, you have to do calculations over CURRENTGROUP() which generally means you are using the X aggregation functions, MAXX, SUMX, etc. If you try to MAXX(FILTER(CURRENTGROUP()... you will get an error.

 

Sample data and expected output might shed some light on a better way of doing things.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

I realize what you mean by including ALL on the fact table rather than using the relational model. So to fix my performance issue I've converted it to DATESBETWEEN and used the relational calendar table.

 

Thank you for the insight!

 

Channing

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.