Calculating a regions column based on multiple criteria

[pcuezze]

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 let me know if it is not.  The logic I need applied is at the bottom.  I know this should be easy, but I can't wrap my head around it.  Thanks in advance!

[ChrisMendoza · Accepted solution]

@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:

[ChrisMendoza · Accepted solution]

@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:

[pcuezze · Accepted solution]

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