Performance Tune Summarize function to GroupBy Function with Filters

[cworkman2015]

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

[Anonymous]

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

[Greg_Deckler · Accepted solution]

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

[cworkman2015]

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