Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pcuezze
Frequent Visitor

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!

 

ifthenregion.png

2 ACCEPTED SOLUTIONS
ChrisMendoza
Resident Rockstar
Resident Rockstar

@pcuezze -

 

With this relationship:

0.PNG

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:

1.PNG

 






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



View solution in original post

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

View solution in original post

2 REPLIES 2
ChrisMendoza
Resident Rockstar
Resident Rockstar

@pcuezze -

 

With this relationship:

0.PNG

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:

1.PNG

 






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.