Forum Discussion
Solved
Calculating a regions column based on multiple criteria
I need a new column in my "Facility Table" based on several If-Then logics (some from within the Facility table, some based on another table). I think this graphic is self-explanatory, but please le...
pcuezze -
With this relationship:
the following as a calculated field seems to do the trick with your sample data:
Region (Calculated) = SWITCH ( TRUE (), 'Facility Table'[State] = "Kansas", "KS", LOOKUPVALUE ( 'Zip Code Table'[Zip Code], 'Zip Code Table'[Zip Code], 'Facility Table'[Zip] ) = 'Facility Table'[Zip], RELATED ( 'Zip Code Table'[Region] ), "m02" )result:
Perfect. The Switch True() trick is pretty neat. I didn't understand it at first so I'm glad I asked for a better solution than a bunch of confusing if, thens!!
Patrick
ChrisMendoza
Resident Rockstar
Resident Rockstarpcuezze -
With this relationship:
the following as a calculated field seems to do the trick with your sample data:
Region (Calculated) =
SWITCH (
TRUE (),
'Facility Table'[State] = "Kansas", "KS",
LOOKUPVALUE (
'Zip Code Table'[Zip Code],
'Zip Code Table'[Zip Code], 'Facility Table'[Zip]
) = 'Facility Table'[Zip], RELATED ( 'Zip Code Table'[Region] ),
"m02"
)result:
Perfect. The Switch True() trick is pretty neat. I didn't understand it at first so I'm glad I asked for a better solution than a bunch of confusing if, thens!!
Patrick