Forum Discussion

Jmccoy's avatar
Jmccoy
Icon for Helper II rankHelper II
6 years ago
Solved

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? 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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]))

     

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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]))