Forum Discussion

Jack42's avatar
Jack42
Regular Visitor
7 years ago
Solved

Fill a column based on a value in another column

Hi,  I need to solve a selecting issue, didn't find something for weeks now. :smileysurprised:   I have a list of items (not unique) with entries for several dates (Timestamp). Each item has a se...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Jack42 
    I did it in 3 stages.

    1. Find if timestamp before deadline - Is Before Dead = IF(Table1[Timestamp] < Table1[Deadline], 1,0)

    2. Find the max date  -

    MAXDATE = CALCULATE(MAX(Table1[Timestamp]),
    FILTER(Table1,Table1[Item Number]=EARLIER(Table1[Item Number])),
    FILTER(Table1,Table1[Is Before Dead] = 1)
    )
     
    3. Find the goal record - Goal Record = IF(Table1[MAXDATE] = Table1[Timestamp], 1,0)
     
    Then filter by Goal Record = 1
     
d_gosbell's avatar
d_gosbell
Icon for Super User rankSuper User
7 years ago

If you want to create this as a calculated column you could use an expression like the following

 

Last Team = 
VAR _currentItem = Table1[Item Number]
VAR _lastTimeBeforeDeadline = MAXX(FILTER(table1, Table1[Item Number] = _currentItem && Table1[Timestamp] < Table1[Deadline]),  Table1[Timestamp])
VAR _lastTeamBeforeDeadline = LOOKUPVALUE(Table1[Team], Table1[Item Number] , _currentItem, Table1[Timestamp], _lastTimeBeforeDeadline)
return _lastTeamBeforeDeadline
  • Jack42's avatar
    Jack42
    Regular Visitor
    7 years ago

    Dear d_gosbell,

    many thanks for your input... unfortunately I could not bring that statement to life - I do something wrong here... Anyhow, the other answer I got was successful, so you do not need to spend more effort in here.

    Please again, many thanks for your effort and time! Have a nice day!

    Jack 🖖