Create a new column using conditions

Question

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 createdBuildingServiceSupplierPurchase Value €OrdineSupplier Administration serviceMilanCleaning servicePippo&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100.0001ABC SpaFlorenceCleaning serviceFranco&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 50.0002WYZ SrlRomeCleaning servicePippo&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 60.0003KKK SpaRome 2Cleaning serviceCarl&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20.000120"No one"VeniceCleaning serviceTizio&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 80.0004KKK SpaTourinCleaning serviceCaio&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 90.0005ABC SpaParisCleaning serviceSempronio&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100.0006ABC SpaBoulogneCleaning serviceSempronio&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 80.0007WYZ SrlMilanAdministration serviceABC Spa&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10.0008&nbsp;FlorenceAdministration serviceWYZ Srl&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10.0009&nbsp;RomeAdministration serviceKKK Spa&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10.00010&nbsp;VeniceAdministration serviceKKK Spa&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10.00011&nbsp;TourinAdministration serviceABC Spa&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10.00012&nbsp;ParisAdministration serviceABC Spa&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10.00013&nbsp;BoulogneAdministration serviceWYZ Srl&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10.00014&nbsp;MilanOther servicesMonica&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 15.000100&nbsp;FlorenceOther servicesSabrina&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20.000101&nbsp;RomeOther servicesDenise&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10.000102&nbsp;RomeOther servicesLilly&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12.000103&nbsp;VeniceOther servicesSabrina&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14.000104&nbsp;TourinOther servicesAnnalisa&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 16.000105&nbsp;ParisOther servicesMaria&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 11.000106&nbsp;BoulogneOther servicesSempronio&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 13.000107&nbsp;&nbsp;

thxalot · Accepted Answer

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

Forum Discussion

Create a new column using conditions

1 Reply

Related content

Create measure to return value from column when condition met for two other columns

Create conditional index column

Create columns group

Conditional Column with Multiple Conditions

Create Conditional Column Based Upon Two Other Columns

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