Forum Discussion
extract column heading based on condition from another table
I have two tables. One is Product table has two columns., Product and Price.
Product table
| Product | Price |
| A | 6.5 |
| A | 4.2 |
| B | 75 |
| C | 28 |
| B | 43 |
| C | 62 |
| A | 2.2 |
The another table has Price range for each product (categorised by Range1, Range2, Rabge3 and Range4)
PriceRangeTable
| Product | Range1 | Range2 | Range3 | Range4 |
| A | <=3 | >3 - <=5 | >5 - <=7 | >7 |
| B | <=15 | >15 - <=25 | >25 - <=50 | >50 |
| C | >75 | <75 - >=50 | <50 - >=25 | <25 |
I want a Calculated Column (Range) in Product table based on the price that fits in the range for specific product
Output Table could be
| Product | Price | Range |
| A | 6.5 | Range3 |
| A | 4.2 | Range2 |
| B | 75 | Range4 |
| C | 28 | Range3 |
| B | 43 | Range3 |
| C | 62 | Range2 |
| A | 2.2 | Range1 |
Hi, Anonymous
I am not sure how your whole data model looks like, but in my opinion, it is good to have the PriceRange-Table structure like below. It is easy in Power Query to change like below.
Then calculated column can be created in an efficient way.
https://www.dropbox.com/s/ycs1w4547kkhhid/eswar.pbix?dl=0
Ranges CC =VAR prices = 'Product'[Price]VAR products = 'Product'[Product]RETURNCALCULATE (SELECTEDVALUE ( PriceRange[Range] ),FILTER (PriceRange,PriceRange[Product] = products&& prices >= PriceRange[Min]&& prices < PriceRange[Max]))Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
3 Replies
Hi, Anonymous
I am not sure how your whole data model looks like, but in my opinion, it is good to have the PriceRange-Table structure like below. It is easy in Power Query to change like below.
Then calculated column can be created in an efficient way.
https://www.dropbox.com/s/ycs1w4547kkhhid/eswar.pbix?dl=0
Ranges CC =VAR prices = 'Product'[Price]VAR products = 'Product'[Product]RETURNCALCULATE (SELECTEDVALUE ( PriceRange[Range] ),FILTER (PriceRange,PriceRange[Product] = products&& prices >= PriceRange[Min]&& prices < PriceRange[Max]))Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
Anonymous You may need to have value in numberic form in your price range table, else it is difficult to perform calculation. There is already a solution for similar problem, please refer to below link, it will take you in the right direction.
https://community.powerbi.com/t5/Desktop/Lookup-Price-Range-for-Style/m-p/322514#M143719
