cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ntaylo06 Regular Visitor
Regular Visitor

Getting Data From Previous Row Based On Two Criteria

I have have the table 'Data' with the fields 'employee ID', 'rotation number', and 'location' with each employee ID appearing three times with rotation number increasing each time and a location code per row. I am trying to add a 'last location' calculated column that will display the last location by looking at the rotation number and subtracting 1 from it, unless the rotation number is 1 which gets the string "xxxxx" like this:

 

+---------------+-------------------+------------+-----------------+
|  employee ID  |  rotation number  |  location  |  last location  |
+---------------+-------------------+------------+-----------------+ 
1001 1 abcde xxxxx 1001 2 fghij abcde 1001 3 klmno fghij 1002 1 pqrst xxxxx 1002 2 uvwx pqrst 1002 3 yzab uvwx

Can anyone help with this?

1 ACCEPTED SOLUTION

Accepted Solutions
ntaylo06 Regular Visitor
Regular Visitor

Re: Getting Data From Previous Row Based On Two Criteria

@HotChilli Thats alright, I found a workaround solution by adding an index column and then calculating index-1 from it. Thanks for trying, though.

View solution in original post

5 REPLIES 5
HotChilli New Contributor
New Contributor

Re: Getting Data From Previous Row Based On Two Criteria

Is the last value in 'last location' a mistake? Should it be uvwx?

 

If so, try adding a column

Column = var _emp = Table1[employee ID]
var _rot = Table1[rotation number]
RETURN
if (Table1[rotation number] <> 1, 
CALCULATE(MAX(Table1[location]),FILTER(Table1, Table1[employee ID] = _emp && Table1[rotation number] < _rot)),
"xxxxx")
ntaylo06 Regular Visitor
Regular Visitor

Re: Getting Data From Previous Row Based On Two Criteria


@HotChilli wrote:

Is the last value in 'last location' a mistake? Should it be uvwx?

Yes, that was a mistake. I've fixed it now.


@HotChilli wrote:

If so, try adding a column


The column kinda works, but it is only pulling the entry for rotation #2 and then displaying it for all rows after the first:

+---------------+-------------------+------------+-----------------+
|  employee ID  |  rotation number  |  location  |  last location  |
+---------------+-------------------+------------+-----------------+ 
1001 1 abcde xxxxx 1001 2 fghij abcde 1001 3 klmno abcde 1002 1 pqrst xxxxx 1002 2 uvwx pqrst 1002 3 yzab pqrst

 

HotChilli New Contributor
New Contributor

Re: Getting Data From Previous Row Based On Two Criteria

Do you want to link your pbix here and i'll have a look at it?

ntaylo06 Regular Visitor
Regular Visitor

Re: Getting Data From Previous Row Based On Two Criteria

@HotChilli Thats alright, I found a workaround solution by adding an index column and then calculating index-1 from it. Thanks for trying, though.

View solution in original post

Community Support Team
Community Support Team

Re: Getting Data From Previous Row Based On Two Criteria

@ntaylo06 

 

Glad to hear that. You may help accept the solution above. Your contribution is highly appreciated.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 6 members 2,627 guests
Please welcome our newest community members: