Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |