Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Need help to create custom column

Hi Team,

 

Could you please me to create custom column for below sample data:

Logic:  need to update the same status for duplicate domains based on latest notification date.

Example: Domain A, have duplicate records, based on latest date(13/8/2019) , need to create custom column(Result) to update the status.

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous,

    You can write a variable to lookup the latest date based on current domain, then you can simply use current domain and latest date variable to find out the last status.

    column =
VAR _lastdate =
    CALCULATE (
        MAX ( Table[Date] ),
        FILTER ( Table, [Domain] = EARLIER ( Table[Domain] ) )
    )
RETURN
    CALCULATE (
        MAX ( Table[Status] ),
        FILTER ( Table, [Domain] = EARLIER ( Table[Domain] ) && [Date] = _lastdate )
    )

    Regards,

    Xiaoxin Sheng

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Hi Anonymous 
    try this
     
    Column =

    var max_date__=calculate(max('Table'[date]),ALLEXCEPT('Table','Table'[Domain]))
    return
    LOOKUPVALUE('Table'[Status],'Table'[Date],max_date__)
     
    Thanks & regards,
    Pravin Wattamwar
    www.linkedin.com/in/pravin-p-wattamwar

    If I resolve your problem Mark it as a solution and give kudos.

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous,

    You can write a variable to lookup the latest date based on current domain, then you can simply use current domain and latest date variable to find out the last status.

    column =
VAR _lastdate =
    CALCULATE (
        MAX ( Table[Date] ),
        FILTER ( Table, [Domain] = EARLIER ( Table[Domain] ) )
    )
RETURN
    CALCULATE (
        MAX ( Table[Status] ),
        FILTER ( Table, [Domain] = EARLIER ( Table[Domain] ) && [Date] = _lastdate )
    )

    Regards,

    Xiaoxin Sheng