Create a new column using conditions

[Paskk]

Hi, I would like to create the "Supplier administration service" column in the input table in which the first corresponding valid value of Supplier is indicated for each distinct building in the context where Service = "Cleaning service". Thank you very much.

This is the table with the expected results in the column to be created

Building	Service	Supplier	Purchase Value €	Ordine	Supplier Administration service
Milan	Cleaning service	Pippo	100.000	1	ABC Spa
Florence	Cleaning service	Franco	50.000	2	WYZ Srl
Rome	Cleaning service	Pippo	60.000	3	KKK Spa
Rome 2	Cleaning service	Carl	20.000	120	"No one"
Venice	Cleaning service	Tizio	80.000	4	KKK Spa
Tourin	Cleaning service	Caio	90.000	5	ABC Spa
Paris	Cleaning service	Sempronio	100.000	6	ABC Spa
Boulogne	Cleaning service	Sempronio	80.000	7	WYZ Srl
Milan	Administration service	ABC Spa	10.000	8
Florence	Administration service	WYZ Srl	10.000	9
Rome	Administration service	KKK Spa	10.000	10
Venice	Administration service	KKK Spa	10.000	11
Tourin	Administration service	ABC Spa	10.000	12
Paris	Administration service	ABC Spa	10.000	13
Boulogne	Administration service	WYZ Srl	10.000	14
Milan	Other services	Monica	15.000	100
Florence	Other services	Sabrina	20.000	101
Rome	Other services	Denise	10.000	102
Rome	Other services	Lilly	12.000	103
Venice	Other services	Sabrina	14.000	104
Tourin	Other services	Annalisa	16.000	105
Paris	Other services	Maria	11.000	106
Boulogne	Other services	Sempronio	13.000	107

[ThxAlot · Accepted solution]

Admin Service = 
IF(
    BLDG[Service] = "Cleaning service",
    COALESCE(
        CALCULATE(
            MAX( BLDG[Supplier] ),
            ALLEXCEPT( BLDG, BLDG[Building] ),
            BLDG[Service] = "Administration service"
        ),
        "No One"
    )
)