Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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.
The code, when thrown at DAX Formatter by SQLBI, ends in an error. Please double-check the correctness of your measure.
@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.
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
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
13 | |
11 | |
9 | |
6 |