Forum Discussion

alexbjorlig's avatar
alexbjorlig
Helper IV
4 years ago
Solved

How to group table by date

I'm new to DAX, and need to find the latest value by date in a measure.   Input:   category     date value1 value2 A 3/3/2020 04:30:00 1 0 A 2/1/2020 04:30:00 0 1 A 1/1/2020...
DAX
Need Help
  • Fowmy's avatar
    Fowmy
    4 years ago

    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)

)

     



Fowmy's avatar
Fowmy
Super User
4 years ago

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. 

alexbjorlig's avatar
alexbjorlig
Helper IV
4 years ago

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"?