Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jack42
Regular 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
Anonymous
Not applicable

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
Anonymous
Not applicable

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

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 👍🏻

d_gosbell
Super User
Super User

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.