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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

How to SUMX a column created with SUMMARIZECOLUMNS

I have the following DAX statement

 

SUMMARIZECOLUMNS(
'Customer'[CustomerID]
,'Reporting Date'[YYYYMM]
,"New Column", <some logic>
)

and when I run this in an SSMS query, column [New Column] is returning the values I want. Now, in Power BI I have created this measure to return the sum of this new column:

Measure for New Column =
    SUMX(
        SUMMARIZECOLUMNS(
            'Customer'[CustomerNumber]
            ,'Reporting Date'[YYYYMM]
            ,"New Column", <some logic>
        )
        ,[New Column]
    )

But I receive this error:

Calculation error in measure: 'Measure for New Column': SummarizeColumns() and AddMissingItemsd() may not be used in this context.

 

Any ideas how I can, within a measure,  sum the columns created in a SUMMARIZECOLUMNS statement?

2 REPLIES 2
daxer-almighty
Solution Sage
Solution Sage

SUMMARIZECOLUMNS is only for queries, not for measures, since this function can't be used in context transition. Secondly, SUMMARIZE is for grouping only, not for any calculations (despite the documentation saying to the contrary). To simulate SUMMARIZE with calculations, you should use the hybrid: ADDCOLUMNS/SUMMARIZE. It's the only reliable way to do what you want. If you want to know why, you can read this: https://www.sqlbi.com/articles/all-the-secrets-of-summarize/

 

You cannot use SUMMARIZECOLUMNS as the first parameter of a SUMX.  You should use SUMMARIZE instead.  Make sure the first parameter of SUMMARIZE is the fact table connected to your dimension tables.



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.