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

[JonnyH]

Hi,

I'm still new to Power BI but I recently upgraded my version of Power BI and now one of my formulas is returning the following error.

The formula is ASQ = SUMMARIZECOLUMNS(Sprints[Qtr],FILTER(Sprints,Sprints[Start]>=RawDataExtract[Snapshot Date]))

This was working before the upgrade, Any pointers really appreciated

all the best

J

[powerbi_zone]

Its 27th July 2022 and I presume this problem still persists! I just did the below calculation using SUMMARIZE (inside a measure).Seems to be working fine. But when i use SummarizeColumn it gives the famous error "Error Message: MdxScript(Model) (28, 5) Calculation error in measure 'financials'[SummarizeColumns]: SummarizeColumns() and AddMissingItems() may not be used in this context." ********************Summarize*********************

Summarize =

var tab1=

SUMMARIZE(financials,

financials[Country],

financials[Year],

"S1",sum(financials[ Sales]) )

var a=sumx(tab1,[S1])

return a

********************SummarizeColumn****************

SummarizeColumns =

var tab2=

SUMMARIZECOLUMNS (

financials[Country],

financials[Year],

"S2",

sum(financials[ Sales]) )

var b=sumx(tab2,Sumx(tab2,[S2]))

return b

*******************End Of Code**************************

In nutshell below is my observation:

When creating a table try using SummarizeColumns instead of Summarize .Remember SummarizeColumns is quicker .

When creating a measure try using Summarize since Summarize column will not work in Context transition (in Tables, Matrix ,Charts) .It only works in cards.

P.S: I am making a video for community and do not want to give out any incorrect info .Hope my observation is correct !

Summarize vs SummarizeColumns

[Anonymous]

Try this.

ASQ=calculatetable(summerize(Sprints,Sprints[Qtr]),FILTER(Sprints,Sprints[Start]>=RawDataExtract[Snapshot Date])

[v-yulgu-msft]

Hi @JonnyH,

Based on my test, the SUMMARIZECOLUMNS function works fine in the new version of PowerBI (2.40.4554.421 64-bit (October 2016)). So, did you change anything in your report after upgrading? Were the datasource, table view and formula exactly the same as before? 

What is the error message? Please provide some sample data and all the relative formulas for further analysis so that I can reproduce your scenario.

Best regards,
Yuliana Gu

[JonnyH]

Hi @v-yulgu-msft,

The error message is the same as in the subject line i.e. 'SummarizeColumns() and AddMissingItems() may not be used in this context' my version is Version: 2.40.4554.463 64-bit (October 2016), I don't believe I changed anything.

Basically all I want to do is take a Date field in my Transaction table, and look that up in a sprint table which has a Quarters column, so basically I have a transaction table that has things like user stories in it 

Transaction table has coluumns:

SnapShotDate,    User Story No.,   Description, Story Points, etc, etc 

And sprints table has columns

Sprint Start Date,   Sprint End Date,  Qtr No 

All I want to do is look up the snapshotdate from the transaction table and return which Sprint number and Quarter the user story was delivered (the Sprint Qtr can differ from Calendar Qtr)

[v-yulgu-msft]

Hi @JonnyH,

I am still confused about your requirement that "look up the snapshotdate from the transaction table and return which Sprint number and Quarter the user story was delivered". If possible, would you please use some sample data, and provide an image to show your expect result?

Thanks,
Yuliana Gu

[zibbyroman]

I am suffering from the same symptoms. The statement below stopped working after the January 2017 upgrade.

Activity =
GENERATE (
FILTER ( SUMMARIZECOLUMNS ( 'Smartcards'[Smartcard ID] ), 'Smartcards'[Smartcard ID] <> BLANK () ) ,
FILTER ( SUMMARIZECOLUMNS ( 'all trips'[Operations Date]), 'all trips'[Operations Date] <> BLANK () )
)

It now comes back with "SummarizeColumns() and AddMissingItems() may not be used in this context." I am certain that no changes were made to the code or even to the data.

Please help.

Zibby

[reubster]

Hi,

I'm no expert, but according to the Definitive Guide to DAX - p256-257, SUMMARIZECOLUMNS cannot have an outside filter context. If it worked before, maybe it shouldn't have.

However SQLBI.com has this update from a few days ago.. "UPDATE 2017-02-01: 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. Please, evaluate whether SUMMARIZE can work in all the conditions you want to support before using it in a measure."

You could write your query like this;

Activity =
GENERATE (
    SUMMARIZECOLUMNS (
        'Smartcards'[Smartcard ID],
        CALCULATETABLE ( 'Smartcards', 'Smartcards'[Smartcard ID] <> BLANK () )
    ),
    SUMMARIZECOLUMNS (
        'all trips'[Operations Date],
        CALCULATETABLE ( 'all trips', 'all trips'[Operations Date] <> BLANK () )
    )
)

See

http://www.sqlbi.com/articles/introducing-summarizecolumns

[zibbyroman]

I am suffering from the same symptoms. The statement below stopped working after the January 2017 upgrade.

Activity =
GENERATE (
FILTER ( SUMMARIZECOLUMNS ( 'Smartcards'[Smartcard ID] ), 'Smartcards'[Smartcard ID] <> BLANK () ) ,
FILTER ( SUMMARIZECOLUMNS ( 'all trips'[Operations Date]), 'all trips'[Operations Date] <> BLANK () )
)

It now comes back with "SummarizeColumns() and AddMissingItems() may not be used in this context." I am certain that no changes were made to the code or even to the data.

Please help.

Zibby