Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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]
)
)
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
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.
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]
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |