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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
JonnyH
Frequent Visitor

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

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

8 REPLIES 8
powerbi_zone
Frequent Visitor

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

Try this.

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

v-yulgu-msft
Employee
Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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)

 

 

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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

 

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.