Summarize ADDCOLUMNS by MAX([@SomeColumn]) gives "Cannot identify table that contains [@SomeColumn]"

[JeffWeir]

I'm looking at trends in names over the last 120 years. (As you do).  

I've got a DAX statement that returns the maximum increase [@Delta] for each name that occurs within a rolling 5 year period. I want to do the equivalent of an SQL GROUP BY on [Name] and MAX(@Delta). But I can't work out how to get my outer SUMMARIZE to recognise my [@Delta] column.

Here's my DAX and sample output that returns my intermediate table:

EVALUATE
ADDCOLUMNS (
    ADDCOLUMNS (
        GENERATE (
            ADDCOLUMNS ( GENERATESERIES ( 1900, 2020, 1 ), "@EndYear", [Value] + 5 ),
            VALUES ( DistinctNames[Name] )
        ),
        "@MinValue", MINX (
            FILTER (
                BabyNames,
                BabyNames[Year] >= [Value]
                    && BabyNames[Year] <= [@EndYear]
            ),
            Babynames[Count]
        ),
        "@MaxValue", MAXX (
            FILTER (
                BabyNames,
                BabyNames[Year] >= [Value]
                    && BabyNames[Year] <= [@EndYear]
            ),
            Babynames[Count]
        )
    ),
    "@Delta", [@MaxValue] - [@MinValue]
)

 ...which returns the results I expect: the max delta within a rolling 5 year period:

But now I want to group by name and MAX(Delta). Basically I want to find the maximum increase across any rolling 5 year period for each and every name.

I tried wrapping the above in SUMMARISE, but DAX studio gives me the error "Cannot identify the table that contains [@Delta] column":

EVALUATE
GROUPBY (
    ADDCOLUMNS (
        ADDCOLUMNS (
            GENERATE (
                ADDCOLUMNS ( GENERATESERIES ( 1900, 2020, 1 ), "@EndYear", [Value] + 5 ),
                VALUES ( DistinctNames[Name] )
            ),
            "@MinValue", MINX (
                FILTER (
                    BabyNames,
                    BabyNames[Year] >= [Value]
                        && BabyNames[Year] <= [@EndYear]
                ),
                Babynames[Count]
            ),
            "@MaxValue", MAXX (
                FILTER (
                    BabyNames,
                    BabyNames[Year] >= [Value]
                        && BabyNames[Year] <= [@EndYear]
                ),
                Babynames[Count]
            )
        ),
        "@Delta", [@MaxValue] - [@MinValue]
    ),
    [Name],
    "@MaxDelta", SUM ( [@Delta] )
)

I've tried using SUMX and a Var, but that returns the max Delta for the entire dataset for each name, i.e.

EVALUATE
VAR GetMax =
    ADDCOLUMNS (
        ADDCOLUMNS (
            GENERATE (
                ADDCOLUMNS ( GENERATESERIES ( 1900, 2020, 1 ), "@EndYear", [Value] + 5 ),
                VALUES ( DistinctNames[Name] )
            ),
            "@MinValue", MINX (
                FILTER (
                    BabyNames,
                    BabyNames[Year] >= [Value]
                        && BabyNames[Year] <= [@EndYear]
                ),
                Babynames[Count]
            ),
            "@MaxValue", MAXX (
                FILTER (
                    BabyNames,
                    BabyNames[Year] >= [Value]
                        && BabyNames[Year] <= [@EndYear]
                ),
                Babynames[Count]
            )
        ),
        "@Delta", [@MaxValue] - [@MinValue]
    )
RETURN
    SUMMARIZE ( GetMax, [Name], "TheMax", MAXX ( GetMax, [@Delta] ) )

Anybody got any pointers? @OwenAuger? 

[OwenAuger]

@JeffWeir 

I see there is already a solution, but just offering another take on it.

One question: Is BabyNames[Count] a measure? It seems that is has to be a measure (rather than a column) otherwise DistinctNames[Name] wouldn't be added to the filter context when evaluating MinCount and MaxCount in your original expression. I would recommend omitting the table name when referencing a measure to avoid confusion with a column reference 🙂

In any case, I would be tempted to write something like the below, which skips the intermediate table but uses nested MAXX functions.

EVALUATE
VAR Years =
    ADDCOLUMNS (
        SELECTCOLUMNS ( GENERATESERIES ( 1900, 2020, 1 ), "@StartYear", [Value] ),
        "@EndYear", [@StartYear] + 5
    )
RETURN
    ADDCOLUMNS (
        VALUES ( DistinctNames[Name] ),
        "TheMax",
        MAXX (
            Years,
            CALCULATE (
                MAXX ( BabyNames, BabyNames[Count] ) - MINX ( BabyNames, BabyNames[Count] ),
                TREATAS ( GENERATESERIES ( [@StartYear], [@EndYear] ), BabyNames[Year] )
            )
        )
    )

I'm assuming your BabyNames table has one row per Name/Year combination?

Might need tweaking if I've misinterpreted the data model in some way.

All the best,

Owen

[MartynRamsden]

Hi @JeffWeir 

Does this work?

EVALUATE
VAR GetMax =
    ADDCOLUMNS (
        ADDCOLUMNS (
            GENERATE (
                ADDCOLUMNS ( GENERATESERIES ( 1900, 2020, 1 ), "@EndYear", [Value] + 5 ),
                VALUES ( DistinctNames[Name] )
            ),
            "@MinValue", MINX (
                FILTER (
                    BabyNames,
                    BabyNames[Year] >= [Value]
                        && BabyNames[Year] <= [@EndYear]
                ),
                Babynames[Count]
            ),
            "@MaxValue", MAXX (
                FILTER (
                    BabyNames,
                    BabyNames[Year] >= [Value]
                        && BabyNames[Year] <= [@EndYear]
                ),
                Babynames[Count]
            )
        ),
        "@Delta", [@MaxValue] - [@MinValue]
    )
RETURN
    SUMMARIZE ( GetMax, [Name], "TheMax", CALCULATE ( MAX ( [@Delta] ) ) )

Best regards,

Martyn

If I answered your question, please help others by accepting it as a solution.

[jonnywinters · Accepted solution]

Hi Jeff,

The following should work. I have used the EARLIER function that accesses the outer row context.

EVALUATE

VAR TempTable = 
    ADDCOLUMNS (
        ADDCOLUMNS (
            GENERATE (
                ADDCOLUMNS ( GENERATESERIES ( 1900, 2020, 1 ), "@EndYear", [Value] + @UserInput ),
                /*FILTER(*/
                VALUES ( DistinctNames[Name] )
            ),
            "@MinValue",MINX (
                FILTER (
                    BabyNames,
                    BabyNames[Year] >= EARLIER( [Value] )
                        && BabyNames[Year] <= EARLIER( [@EndYear] ) && BabyNames[Name]=EARLIER( [Name] )
                ),
                Babynames[Count]
            ),
            "@MaxValue", MAXX (
                FILTER (
                    BabyNames,
                    BabyNames[Year] >= EARLIER( [Value] )
                        && BabyNames[Year] <= EARLIER( [@EndYear] ) && BabyNames[Name]=EARLIER( [Name] )
                ),
                Babynames[Count]
            )
        ),
        "@Delta", [@MaxValue] - [@MinValue]
    )
    
RETURN

ADDCOLUMNS(
	TempTable,
	"MaxName",
	MAXX(
		FILTER(
			TempTable,
			[Name] = EARLIER( [Name] )
		),
		[@Delta]
	)
)