Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
Hello,
my original table:
Category | Date | Name | ||
A | 25.01.2022 | Pan | ||
B | 25.01.2022 | Hook | ||
A | 24.01.2022 | Hook | ||
B | 24.01.2022 | Pan | ||
A | 23.01.2022 | Pan | ||
B | 23.01.2022 | Hook |
How to make a calculated table which in any case always only shows the newest date for both categories ?
Expected outcome like this:
Category | Date | Name | ||
A | 25.01.2022 | Pan | ||
B | 25.01.2022 | Hook |
I tried addcolumns with summarize and calculated max for date and name. It might works for date but somehow there is no context tranisition for the name. Instead it will show the name with the "higher" alphabet. The name can be wrong.
Thank you very much in advance.
Best.
Solved! Go to Solution.
Hi @Applicable88
For table you can use
Last Data =
VAR DataWithLastDatePerCategory =
ADDCOLUMNS (
Data,
"@LastDate",
VAR CategoryTable =
CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Category ] ) )
RETURN
MAXX (
CategoryTable,
Data[Date]
)
)
VAR Result =
FILTER ( DataWithLastDatePerCategory, Data[Date] = [@LastDate] )
RETURN
SELECTCOLUMNS ( Result, "Category", [Category ], "Date", [Date], "Name", [Name] )
for Calculated column you can use
Name On Last Date =
VAR CategoryTable =
CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Category ] ) )
VAR MaxDate =
MAXX (
CategoryTable,
Data[Date]
)
VAR Result =
MAXX (
FILTER ( CategoryTable, Data[Date] = MaxDate ),
Data[Name]
)
RETURN
Result
@Applicable88 Try with:
@Applicable88 Try with:
Hi @Applicable88
For table you can use
Last Data =
VAR DataWithLastDatePerCategory =
ADDCOLUMNS (
Data,
"@LastDate",
VAR CategoryTable =
CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Category ] ) )
RETURN
MAXX (
CategoryTable,
Data[Date]
)
)
VAR Result =
FILTER ( DataWithLastDatePerCategory, Data[Date] = [@LastDate] )
RETURN
SELECTCOLUMNS ( Result, "Category", [Category ], "Date", [Date], "Name", [Name] )
for Calculated column you can use
Name On Last Date =
VAR CategoryTable =
CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Category ] ) )
VAR MaxDate =
MAXX (
CategoryTable,
Data[Date]
)
VAR Result =
MAXX (
FILTER ( CategoryTable, Data[Date] = MaxDate ),
Data[Name]
)
RETURN
Result
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
6 | |
3 | |
3 | |
3 |
User | Count |
---|---|
13 | |
11 | |
8 | |
8 | |
8 |