SummarizeColumns() may not be used in this context

[Anonymous]

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.

[Anonymous · Accepted solution]

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]

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

Blog: WhatTheFact.bi

[Anonymous]

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