Give a rank by pricing range

[primolee]

Hello everyone,

I am not sure which is better to do in DAX or Power Query, but I am looking for a solution to give ranking by pricing range.

I have the following two tables:

Car List Price

Car Model	Car Price
BMW 320	100000
BMW 520	150000
BMW 730	210000

Ranking Chart

Rank	Min	Max
D	1	100000
C	100001	150000
B	150001	200000
A	200001	999999

So, as result, BMW 320 should be Rank D, 520 Rank C and 730 Rank A.  How should I do this in Power Query or DAX?  Thank you so much for your help.

Best regards,

David

[Jihwan_Kim · Accepted solution]

Hi @primolee 

Please check the below picture and the sample pbix file's link down below.

CarPrice Group =
CALCULATE (
[CarPrice Measure],
FILTER (
VALUES ( Cars[Car Model] ),
COUNTROWS (
FILTER (
Ranking,
[CarPrice Measure] >= Ranking[Min]
&& [CarPrice Measure] <= Ranking[Max]
)
) = 1
)
)

https://www.dropbox.com/s/yb3v6ugjxz5dl0f/primolee.pbix?dl=0 

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.

[primolee]

Hello,

Made a little modification to suit my situation, works perfect!  Thank you!

Best regards,

David

[rodrigosan]

Hi,
Would this conditional be useful?

[primolee]

Hello,

This will be hard-coded.  I want min and max values to be dynamic.  Thank you for the help though. 🙂

Best regards,

David