Forum Discussion
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 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!
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
2 Replies
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