cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cworkman2015
Helper II
Helper II

Convert ADDCOLUMNS + SUMMARIZE to SUMMARIZECOLUMNS

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

 

1 REPLY 1
daxer
Solution Sage
Solution Sage

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.

 

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.