Error - Summarise columns and addmissingitems may not be used in this context power bi

[Timmay]

Hey all,

The DAX formula

SUMX(
    SUMMARIZECOLUMNS(
        dates[Date],
        "@Sales",
        [Sales Total]
    ),
    [@Sales]
)

returns the following error

“mdxscript(model) (184, 5) calculation error in measure …
Summarisecolumns and addmissingitems may not be used in this context”

if displayed in a table with the dates context.

My other measure works fine with what i am wanting to achieve:

SUMX(
    ADDCOLUMNS(
        SUMMARIZE(
            dates,
            Dates[Date]
        ),
        "@Sales",
        if(
            [Sales Total] = BLANK(),
            0,
            [Sales Total]
        )
    ),
    [@Sales]
)

I have tried to do it with SUMMARIZE COLUMNS because I read an article on SQLBI (https://www.sqlbi.com/articles/introducing-summarizecolumns/) that was saying that SUMMARIZE is like an old function that is being deprecated and SUMMARIZE COLUMNS should be used instead. But I get an error when trying to use it.

I also searched around, found this previous help topic but it doesn’t really explain why it’s happening.

SummarizeColumns() and AddMissingItems() may not be used in this context

I’ve attached my demo file, sheet is “summarise error” (1st sheet)

Mock Data PBI.pbix (1.5 MB)

I know that the summarise function works but I really want to know why the error comes up in summarize columns, it appears that the new “funky” summarize function doesn’t work like the old one

Any help is appreciated.

Thanks,
Tim

[Harsh]

Hello @Timmay,

Thank You for posting your query onto the Forum.

The reason why it’s resulting in this kind of error is because “SUMMARIZECOLUMNS()” - “Only works in the form of “Evaluation Context” but fails when applied in the form of “Context Transition”.”

So for example, let’s see what’s evalaution context and what’s context transition -

When you use a card visual, the only thing that you put inside a card is a “Measure” and nothing else i.e., no fields from any of the tables. And therefore, since card visual only works with the evaluation context, it’ll showcase you the results when you use the “SUMMARIZECOLUMNS()” function to calculate the results or totals. Below is the screenshot provided for the reference -

Now, when you convert this card visual in any other chart by adding fields from any of the tables that’s when “Context Transition” takes place. So for example, in your case, you wanted to evaluate the results Date wise by adding a date field inside the table visual. So in that case, it needs to evaluate the results for every single day and not at a Total Level and therefore, it failed to evaluate the results. Below are the screenshots provided for the reference -

But now, when you again remove the Date field from the Table visual, it’ll calculate the results since there’s no context transition but only the evaluation context in place i.e., one single figure in the form of Totals. Below is the screenshot provided for the reference -

When one wants to evaluate the results in the form of “Context Transition”, they need to make usage of either “ADDCOLUMNS() combined with SUMMARIZE()” function or use “SUMMARIZE()” function to evaluate the results as a “Context Modifier”.

The link of the article which you’ve provided in that, the first line itself states as follows -

“The SUMMARIZECOLUMNS function is still not fully supported in a modified filter context. For example, it cannot be used within a measure in an expression of ADDCOLUMNS”.

And therefore, that’s the reason why “SUMMARIZE()” function is providing you the results in any kind of visuals but not the “SUMMARIZECOLUMNS()” function since this function only works in the form of “Evaluation Context”, as stated earlier.

Hoping you find this explanation helpful and strengthens your understanding between the usage of these two functions.

Thanks and Warm Regards,
Harsh

[Timmay]

Thanks Harsh, fantastic as usual, I feel like you are my private tutor

I finally understand context transition, and now the SQLBI article makes sense to me. I’ll keep plugging away using SUMMARIZE, it’s a great function, even if Marco says not to use it, works well for me

[Harsh]

Hello @Timmay,

You’re Welcome!!!

I’m glad that I was able to assist you.

Well, even more so you can definitely combine the “SUMMARIZE()” function with the “ADDCOLUMNS()” and it’ll do wonders in terms of performance optimization.

Have a great weekend!

Thanks and Warm Regards,
Harsh