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

SummarizeColumns() may not be used in this context

 

Hi Everyone, I'm trying to check if a customer code is found within a list of customers, I've created the list of customers using the following sytanx and it's working fine, the only issue I'm facing when I call the table within a calclated column.

 

This method works:

 

 

Growth Status = 
IF (
    'Customers'[Customer code]
        in table
,"Positive Growth",BLANK())

 

 

 

table = CALCULATETABLE (
    VALUES ( 'sales'[Customer code] ),
    FILTER (
        SUMMARIZECOLUMNS(
            'sales'[Customer code],
            FILTER (
                'Calendar',
                'Calendar'[Year] = YEAR ( TODAY () )
            ),
            "YTD Growth", [% YTD Sales Vs. LY YTD Sales]
        ),
        [YTD Growth] > 0
    )
)

 

 

 

This method returns an errror "SummarizeColumns() may not be used in this context."

 

 

Growth Status = 
IF (
    'Customers'[Customer code]

        in 

CALCULATETABLE (
    VALUES ( 'sales'[Customer code] ),
    FILTER (
        SUMMARIZECOLUMNS(
            'sales'[Customer code],
            FILTER (
                'Calendar',
                'Calendar'[Year] = YEAR ( TODAY () )
            ),
            "YTD Growth", [% YTD Sales Vs. LY YTD Sales]
        ),
        [YTD Growth] > 0
    )
)

,"Positive Growth",BLANK())

 

 

I'm trying to use the second method as first method is not sufficient in the long run.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

 

I managed to work it out with the following dax syntax, changed the summarizecolumns function to addcolumns function in conjunction with summrize function:

 

 

 

Growth Status = 
IF (
    'Customers'[Customer code]
        IN CALCULATETABLE (
            VALUES ( 'sales'[Customer code] ),
            FILTER (
                CALCULATETABLE (
                    ADDCOLUMNS (
                        SUMMARIZE ( 'sales', 'sales'[Customer code] ),
                        "YTD", CALCULATE ( [% YTD Sales Vs. LY YTD Sales] )
                    ),
                    FILTER (
                        'Calendar',
                        'Calendar'[Year] = YEAR ( TODAY () )          
                    )
                ),
                [YTD] > 0
            )
        ),
    "Positive Growth",BLANK())

 

 

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

 

I managed to work it out with the following dax syntax, changed the summarizecolumns function to addcolumns function in conjunction with summrize function:

 

 

 

Growth Status = 
IF (
    'Customers'[Customer code]
        IN CALCULATETABLE (
            VALUES ( 'sales'[Customer code] ),
            FILTER (
                CALCULATETABLE (
                    ADDCOLUMNS (
                        SUMMARIZE ( 'sales', 'sales'[Customer code] ),
                        "YTD", CALCULATE ( [% YTD Sales Vs. LY YTD Sales] )
                    ),
                    FILTER (
                        'Calendar',
                        'Calendar'[Year] = YEAR ( TODAY () )          
                    )
                ),
                [YTD] > 0
            )
        ),
    "Positive Growth",BLANK())

 

 

selimovd
Super User
Super User

Hey @Anonymous ,

 

you can get the values that are in both tables with the INTERSECT function and VALUES:

MyIntersectTable =
INTERSECT (
    VALUES ( 'Customers'[Customer code] ),
    VALUES ( 'sales'[Customer code] )
)

 

Let me know if that works. If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 
Anonymous
Not applicable

My issue is not with the intersection, it's with the summrizecolumn function

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.