cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jmccoy
Frequent Visitor

DAX- New Column for territory

I have a table that contains project orders and a zip code for that order. I am trying to use a seperate territory zip code table to tie the project and the territories together. in my territory zip code table I have a territory, state, startzip and endzip. Is it possible with dax to make a new column that will take my zip code and look between the start and end zip, then return the territory? 

1 ACCEPTED SOLUTION

Accepted Solutions
Jef Resolver II
Resolver II

Re: DAX- New Column for territory

Assuming my 'Table' is your order table and 'Table (2)' is your area table, the dax formula should look something like this: 

area = CALCULATE(VALUES('Table (2)'[area]); FILTER('Table (2)';'Table'[zip] >= 'Table (2)'[from] && 'Table'[zip] < 'Table (2)'[to]))

 

View solution in original post

2 REPLIES 2
Super User IV
Super User IV

Re: DAX- New Column for territory

Yes, you should be able to do this. You would filter your other table for rows where the start zip is less than your zip code AND the end zip is greater than your zip code. You could then use MAXX to return your territory name from that filtered table.

 

Specifics and testing would require sample data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Jef Resolver II
Resolver II

Re: DAX- New Column for territory

Assuming my 'Table' is your order table and 'Table (2)' is your area table, the dax formula should look something like this: 

area = CALCULATE(VALUES('Table (2)'[area]); FILTER('Table (2)';'Table'[zip] >= 'Table (2)'[from] && 'Table'[zip] < 'Table (2)'[to]))

 

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors