cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
saranp780 Member
Member

How to search the last row of the group and put the text or number?

I have some data like this.

 

The room (number)YearGrade
10220162.07
10220173
10220182.96
10320162.96
10320153.12
10320163.12
10320173.12
10420171.69
10420181.69
10520142.41
10520153.96
10520173.24

 

My requirement is to search the last row of every room number to put some remark in a new column.

Example

Room(number)YearGraderemark
10220162.07 
10220173 
10220182.96Last year of this room
10320162.96 
10320153.12 
10320163.12 
10320173.12Last year of this room
10420171.69 
10420181.69Last year of this room
10520142.41 
10520153.96 
10520173.24Last year of this room

The new column is to remark the last year of the room. So now, what do DAX or measure I have to use?

 

Thank you so much for giving help.

1 ACCEPTED SOLUTION

Accepted Solutions
ValentinBIA Regular Visitor
Regular Visitor

Re: How to search the last row of the group and put the text or number?

Hi @saranp780,

 

In order to ger what you are looking for, you should:

- Go to the Power Query Editor

- Duplicate your Query

- On the duplicated query, group by room number, with the operation max, on the column Year

- Add a custom column (= "Last year of this room")

- Go back to your main Query

- Merge with the duplicated query using the "the room number" as a 1st matching column and "year" as the 2nd matching column (hold control or shift when selecting to have more than one)

- Expand with only the custom column

- There you go !

 

Hope that helps !

 

Best,

 

Valentin

1 REPLY 1
ValentinBIA Regular Visitor
Regular Visitor

Re: How to search the last row of the group and put the text or number?

Hi @saranp780,

 

In order to ger what you are looking for, you should:

- Go to the Power Query Editor

- Duplicate your Query

- On the duplicated query, group by room number, with the operation max, on the column Year

- Add a custom column (= "Last year of this room")

- Go back to your main Query

- Merge with the duplicated query using the "the room number" as a 1st matching column and "year" as the 2nd matching column (hold control or shift when selecting to have more than one)

- Expand with only the custom column

- There you go !

 

Hope that helps !

 

Best,

 

Valentin