Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Calculated Column by Other Column

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.

Need Help
  • rubayatyasmin's avatar
    rubayatyasmin
    3 years ago

    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:

     

     

3 Replies

  • rubayatyasmin's avatar
    rubayatyasmin
    Community Champion
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      perfection, thank you!

  • DOLEARY85's avatar
    DOLEARY85
    Resident Rockstar
    3 years ago

    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 👍