Forum Discussion
How to group table by date
alexbjorlig
Try this table, it will work for any number of columns in your tableNew Table = FILTER( table, VAR __cat = Table[category ] RETURN Table[date] = CALCULATE( MAX(Table[date]) , REMOVEFILTERS() , Table[category ] = __cat) )
alexbjorlig
Try this table, it will work for any number of columns in your table
New Table =
FILTER(
table,
VAR __cat = Table[category ] RETURN
Table[date] = CALCULATE( MAX(Table[date]) , REMOVEFILTERS() , Table[category ] = __cat)
)
I know it'sprobably super hard to explain, but why does this work - I'm so impressed! š
The CALCULATE uses the REMOVEFILTERS() --> but I guess that could be avoided, or what does that exactly do?
And the __cat variable is a refrence to the filter context, making sure it's only done for the "correct" category?
So simple, so elegant š
alexbjorlig
With the Filter function, there is a row context, you only one record at a time, REMOVEFILTERS clears the row context filter and shows all the records and again _cat filters by category obtained from the row context. Now the max date is taken and matched.Amazing.
So they key difference to understand for me here, is the "filter" context vs row context. The REMOVEFILTERS only clears the row context, so any filters there would still "work"?