Forum Discussion
Searching between two columns for a value within a range (Zip Code)?
Hello,
As I begin to migrate from Excel to Power BI, because the office loves the reports :), I keep getting stuck trying to identify formulas that will work in DAX. What a learning curve :).
Anyway, I have a table with customer account information. One column has the customers zipcode, which I have stored as text to support international customers. I have a second table that has three columns. StartZipCode, EndZipCode and RepFirm. In the account table I have created a calculated column call RepFirm. In the calcuated column I put the following formula:
RepFirm = IF(Accounts[Sales Region]="US",IFERROR(LOOKUPVALUE(USRepRegions[REP_NAME],USRepRegions[FROM_ZIP],Accounts[Postal Code]),"UNK"),"UNK")
The code above looks through the beginning zip codes and looks for an exact match :(. I am used to using VLOOKUP and looking for values accross columns and within a range. For example VLOOKUP would use Postal Code 30101 and go to the ZipCode table and find that 30101 is between columns ToZip 30000 and FromZip 32000 and return the rep firm. See example lookup table below.
| FROM_ZIP | TO_ZIP |
| 0 | 600 |
| 601 | 987 |
| 988 | 999 |
| 1000 | 2799 |
| 2800 | 2999 |
| 3000 | 3899 |
| 3900 | 4999 |
| 5000 | 5999 |
| 6000 | 6999 |
| 7000 | 8999 |
| 9000 | 9999 |
| 10000 | 11999 |
| 12000 | 12399 |
| 12400 | 12699 |
| 12700 | 12799 |
| 12800 | 14999 |
| 15000 | 16899 |
| 16900 | 19699 |
| 19700 | 19999 |
| 20000 | 20599 |
| 20600 | 21299 |
| 21300 | 21399 |
| 21400 | 21999 |
| 22000 | 24699 |
| 24700 | 26999 |
| 27000 | 28999 |
| 29000 | 29999 |
| 30000 | 31999 |
| 32000 | 32399 |
| 32400 | 32599 |
| 32600 | 34999 |
| 35000 | 36999 |
| 37000 | 38599 |
| 38600 | 39799 |
| 39800 | 39999 |
Any help or ideas on how to solve this would be greatly appreciated.
2 Replies
This problem is called "banding", "bucketing" or "segmentation". Have a look at this post: http://exceleratorbi.com.au/banding-in-dax/ where you find a DAX-approach or some M-alternatives in the comments. Or here another DAX: http://www.daxpatterns.com/static-segmentation/
One way I can think to make this work, which may or may not work with what you need to accomplish, would be to recreate your zip code ranges table to include every possible unique zip code. This works well within PowerBI's preferred many-rows-few-columns structure, and Zip then becomes a unique identifier that you can connect to the zips in the customer table (a one-to-many relationship). So instead of StarZip, EndZip, and RepFirm, you would have two columns:
Zip RepFirm
0 Firm A
1 Firm A
2 Firm A
3 Firm B
4 Firm B
..... .....
You could also include columns for "ZipRange", "ZipStart", or "ZipEnd" if you need them. Then all you have to do is create a relationship between the customer account table Zip column and the ZipCode table zip column and RepFirm will automatically be matched up with the customer. That would save some formula writing.
