Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
primolee
Helper V
Helper V

Give a rank by pricing range

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 ModelCar Price
BMW 320100000
BMW 520150000
BMW 730210000

 

Ranking Chart

RankMinMax
D1100000
C100001150000
B150001200000
A200001999999

 

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

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi @primolee 

 

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

 

Picture1.png

 

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.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

4 REPLIES 4
Jihwan_Kim
Super User
Super User

Hi @primolee 

 

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

 

Picture1.png

 

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.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Hello,

 

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

 

Best regards,

David

rodrigosan
Resolver III
Resolver III

Hi,
Would this conditional be useful?
Ranking.png

Hello,

 

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

 

Best regards,

David

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.