extract column heading based on condition from another table

[Anonymous]

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

[negi007]

@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

[Jihwan_Kim · Accepted solution]

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]

RETURN

CALCULATE (

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]

Thank you for making the concept (having Min and MAx column for grades) clear.