Max date for User ID - Multiples dates in a Table

[zapppsr]

This one should be simple and I'm not getting it right:

I want to create a Calculated Column that shows the Max date for the ID.

ID 1 has 01/01/2010, 01/01/2011 and 01/01/2012. The Max Date for this ID is 01/01/2012.

Hou can I create a DAX formula to create that Column?

Thanks in advance.

 

[Aladin Akyurek]

In the 3rd column, you can enter:

=MAX([Date])

Try first to add A1:B10 to the data model (Add to the Data Model).

 

[zapppsr]

Hi, thanks for replying.

It does not seem to work. It gets the Max Date of ALL.. not for ID.

 

[Ozeroth]

Try

MaxDate =
CALCULATE
    MAX ( fAntrop[Data] ),
    ALLEXCEPT ( fAntrop, fAntrop[ID] )
)

 

[zapppsr]

Ok, that's perfect, thank you! But, now explain me what happened:

ALLEXCEPT removes all the filters from the table, except for the column specified in its arguments.
Did it use calculate to filter the whole table, except the ID of the current row?

Thanks again!

 

[Ozeroth]

No problem

First of all, CALCULATE (used in a calculated column) turns the current row context into a filter context including all values in the current row (context transition).
But since we only want to use the ID from the current row (rather than all columns), ALLEXCEPT is used to clear all filters except for ID.
ALLEXCEPT ( fAntrop; fAntrop[ID] ) tells CALCULATE to clear all filters except for fAntrop[ID].

The end result is that we have created a filter context containing just a filter on fAntrop[ID] corresponding to the value in the current row.

We can then calculate MAX ( fAntrop[Data] ) in that context to get the maximum date across all rows corresponding to the current row's ID.

 

[zapppsr]

Hi, thanks again for replying.

Maybe the name of the function is misleading. Before seeing your solution, I thought ALLEXCEPT worked like this:

ALL = ID 1, 2 and 3.
ALLEXCEPT ID 1 = ID 2 and 3 - On other words ALL IDs, except the one I'm telling you.

But it does the exact opposite:

ALLEXCEPT ID1 = ID 1

It is kinda confusing isn't it? - How do you reason to understand the behaviour and the name.

 

[Ozeroth]

Yes, I agree the naming of ALL and ALLEXCEPT can be confusing, especially when they are used as top level arguments within CALCULATE.

To quote The Definitive Guide to DAX, "You should read ALL as REMOVEFILTERS".
And I would add you should read ALLEXCEPT as REMOVEFILTERS EXCEPT.

In your example, ALLEXCEPT is removing all filters except ID, so just the current ID remains in the filter context.

By the way, the behaviour you described is something you can do with the EXCEPT function. For example EXCEPT ( ALL ( fAntrop[ID] ), VALUES ( fAntop[ID] ) ), would invert the values of ID currently visible in the filter context.