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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors