Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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:
Proud to be a Super User!
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
@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:
Proud to be a Super User!
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
User | Count |
---|---|
104 | |
92 | |
88 | |
78 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |