The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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