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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors