cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jack42 Frequent Visitor
Frequent Visitor

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. Smiley Surprised

 

I have a list of items (not unique) with entries for several dates (Timestamp).

Each item has a separate due date.

I want to find the last row and then the unique value for "Team" just before the due date has arrived.

 

Item NumberDeadlineTimestampTeam
1061248928.02.2019 09:11:0027.02.2019 10:44:34Team 1
1061248928.02.2019 09:11:0027.02.2019 10:46:30Team 1
1061248928.02.2019 09:11:0028.02.2019 08:48:58Team 2
1061248928.02.2019 09:11:0028.02.2019 12:45:30Team 1
1061248928.02.2019 09:11:0028.02.2019 22:36:25Team 1
1061248928.02.2019 09:11:0001.03.2019 08:27:24Team 1
1061248928.02.2019 09:11:0001.03.2019 19:17:47Team 2
1061694711.03.2019 11:48:0008.03.2019 09:38:57Team 3
1061694711.03.2019 11:48:0008.03.2019 09:52:07Team 4
1061694711.03.2019 11:48:0008.03.2019 09:52:32Team 1
1061694711.03.2019 11:48:0008.03.2019 17:41:37Team 1
1061694711.03.2019 11:48:0011.03.2019 22:24:09Team 1
1061694711.03.2019 11:48:0012.03.2019 08:54:38Team 3
1061704908.03.2019 15:49:0008.03.2019 11:49:54Team 1
1061704908.03.2019 15:49:0008.03.2019 13:25:26Team 1
1061704908.03.2019 15:49:0008.03.2019 13:26:06Team 5
1061704908.03.2019 15:49:0011.03.2019 09:43:11Team 5

 

Solution would be then

Item NumberTeam
10612489Team 2
10616947Team 1
10617049Team 5

 

I did not manage to summarize that or to use calculate, maybe I am not experienced enough.

Any help is greatly appreciated!

 

Many thanks in advance!

Jack

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
AClerk New Contributor
New Contributor

Re: Fill a column based on a value in another column

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
 
2019-03-25 09_35_22-papercut - Remote Desktop Connection.png2019-03-25 09_37_30-papercut - Remote Desktop Connection.png

View solution in original post

4 REPLIES 4
Super User
Super User

Re: Fill a column based on a value in another column

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
Highlighted
AClerk New Contributor
New Contributor

Re: Fill a column based on a value in another column

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
 
2019-03-25 09_35_22-papercut - Remote Desktop Connection.png2019-03-25 09_37_30-papercut - Remote Desktop Connection.png

View solution in original post

Jack42 Frequent Visitor
Frequent Visitor

Re: Fill a column based on a value in another column

Dear AClerk,

 

brilliant, works very good. I learned a lot by that... many thanks!

 

I only needed to replace the kommata in the two „if“ statements with semicoli.

just in case s.o. finds this post and reuses it...

 

Cheers, Jack 👍🏻

Jack42 Frequent Visitor
Frequent Visitor

Re: Fill a column based on a value in another column

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 🖖

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 141 members 1,661 guests
Please welcome our newest community members: