Calculated Column by Other Column

[Anonymous]

Hi all, I'm having trouble coding this column...

my data behaves like this:

and I want to add a new column that will return the Event with the max date, per Subject

like this:

I can't go into the details, the data is proprietary... This is a mock-up of only what I need, and only 3 subjects.

[rubayatyasmin · Accepted solution]

Hi, @Anonymous 

I tried to reproduce the problem. 

I used the dax below

Max Event = 
VAR CurrentSubject = Table1[Subject]
VAR MaxDate = 
    CALCULATE(
        MAX(Table1[Event Date]), 
        FILTER(
            Table1, 
            Table1[Subject] = CurrentSubject
        )
    )
RETURN 
    CALCULATE(
        MAX(Table1[Event]),
        FILTER(
            Table1,
            Table1[Subject] = CurrentSubject && Table1[Event Date] = MaxDate
        )
    )

result:

[Anonymous]

perfection, thank you!

[DOLEARY85]

Hi,

you could try something like this:

column = var _maxdate = CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[Subject]))

return

CALCULATE(VALUES('Table'[Event]),'Table'[Date]=_maxdate,ALLEXCEPT('Table','Table'[Subject]))

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍