Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Good afternoon
I'm attempting some performance tuning with a complex dax calculation and my research has lead me to the use of SUMMARIZECOLUMNS over SUMMARIZE + ADDCOLUMNS.
I was able to accurately calculate average daily sales units over a 6 week period (dropping the high and low week of sales. however, when using this calculation it wrecks my performance. My fact table is named "CONFORM_MOVEMENT" it has about 2.5 million records.
In the image below you can see the variable values_displayed where I calculate the base table. I'm summarizing the daily sales units and time period by Store, Item.
The daily sales units are then used for determining the max and minimum of the 6 week period. Using the additional @Time_period_end column I'm able to determine which week is high and low and then calculate the average where it doesn't equal.
I attempted to convert this to a SUMMARIZECOLUMNS table however I keep getting the error: SummarizeColumns() and AddMissingItems() may not be used in this context. --See variable values_displayed1
Here is my code:
daily_sales_units_normalized =
var values_displayed =
CALCULATETABLE(
ADDCOLUMNS(
SUMMARIZE(FILTER(CONFORM_MOVEMENT,CONFORM_MOVEMENT[is_summary] =1 ), 'Calendar'[time_period_end_DateTime],CONFORM_MOVEMENT[Store], CONFORM_MOVEMENT[ItemNumber]),
"@Amt", [daily_sales_units],
"@time_period_end",'Calendar'[time_period_end_DateTime]),
ALLSELECTED())
var values_displayed1 = SUMMARIZECOLUMNS(DELIVER_STORES[Store], DELIVER_ITEMS[ItemNumber], 'Calendar'[time_period_end_DateTime],
filter(CONFORM_MOVEMENT,CONFORM_MOVEMENT[is_summary] = 1),
"@Amt", [daily_sales_units],
"@time_period_end",SELECTEDVALUE('Calendar'[time_period_end_DateTime]))
var minVal = minX(
FILTER(values_displayed, 'Calendar'[time_period_end_DateTime] > max('Calendar'[time_period_end_DateTime]) - 42
&& 'Calendar'[time_period_end_DateTime] <= max('Calendar'[time_period_end_DateTime])), [@Amt])
var minDate = maxx(
FILTER(values_displayed, [@Amt] = minVal),
[@time_period_end])
var maxVal = maxX(
FILTER(values_displayed, 'Calendar'[time_period_end_DateTime] > max('Calendar'[time_period_end_DateTime]) - 42
&& 'Calendar'[time_period_end_DateTime] <= max('Calendar'[time_period_end_DateTime])), [@Amt])
var maxDate = maxx(
FILTER(values_displayed, [@Amt] = maxVal),
[@time_period_end])
return
averagex(FILTER(ALL('Calendar'),
'Calendar'[time_period_end_DateTime] <> minDate && 'Calendar'[time_period_end_DateTime] <> maxDate),
[daily_sales_units])
Any advice would be greatly appreciated!
Thanks
Channing
Due to the complexity of SUMMARIZECOLUMNS I'd strongly advise against using it in complicated scenarios. Its behaviour is highly unintuitive (meaning you'll be getting figures you won't know how to explain in no time) and the function also has bugs (yes, you heard it right). If you want to know what I'm really talking about, you can have a read of this article by my colleague: The Peculiar Behavior Of SUMMARIZECOLUMNS In DAX | LinkedIn
There's even much more to what Abhinav is talking about in his article but discussing it would take quite a time. By the way, the error you get is very likely a manifestation of what I'm talking about here. I've had such situations in the past as well (getting the same error) and it was not possible to pinpoint what the problem was.
User | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |