Compare rows and delete based on condition

[jonbox]

Hi All, hopefully a simple request;

I have data similar to Table1 where the people in the "Name" column are in different "rooms". What i want is to get to the Table2 where if they're in room 5 specifically, remove all other rooms associated to their name e.g. for Jon, he's in room 5 so remove room 8, same for Ben where because he's in Room 5, remove room 6 and 3, whereas for Dan because he's not in room 5, keep Room 2 and Room 9.

Can anyone help on a formula where you look at all the rows per person, check if room 5 is there, if so delete every row except the one with room 5. If a person doesn't have a row with room 5, keep all the rows.

Hopefully that's clear! Thanks!

Table1:

Name	Room
Jon	Room 5
Jon	Room 8
Ben	Room 6
Ben	Room 3
Ben	Room 5
Alex	Room 8
Alex	Room 5
Alex	Room 2
Dan	Room 2
Dan	Room 9

Table2:

Name	Room
Jon	Room 5
Dan	Room 5
Alex	Room 5
Ben	Room 2
Ben	Room 9

[v-zhangti · Accepted solution]

Hi, @jonbox 

You can try the following methods.

Column:

Check Room 5 = 
Var _table=CALCULATETABLE(VALUES(Table1[Room]),FILTER(ALL(Table1),[Name]=EARLIER(Table1[Name])))
Var _N1=IF("Room 5" in _table,"Room 5",[Room])
Return
IF([Room]=_N1,[Room],BLANK())

New Table:

Table2 = FILTER(Table1,[Check Room 5]<>BLANK())

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.