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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
hixkim
Frequent Visitor

Rank based on multiple fields

Hello - hoping someone is able to help me out with this, I'm a little out of my depth. I think either a column or DAX would work for me. 

Ultimately I want to rank a vendor's weekly pricing lowest to highest based on part number. Since we can get pricing at different times during the week I created a Year-Week column to base this off of. I added a rank column below of how I would like it to work. I highlighted the columns in blue that I believe are necessary to accomplish this, the rest are just for context. 

 

hixkim_1-1633094366840.png

 

Thanks in advance! 

 

1 ACCEPTED SOLUTION
themistoklis
Community Champion
Community Champion

Hello @hixkim 

 

Create a new calculated column with the following formula:

Sorting Column = 
RANKX (
    FILTER ( 'Table', 'Table'[Year-Week] = EARLIER ( 'Table'[Year-Week] ) && 'Table'[Item Number] = EARLIER ( 'Table'[Item Number] ) ),
    'Table'[Unit Cost],
    ,
    ASC,
    DENSE
)

 

Workspace Attached

View solution in original post

3 REPLIES 3
themistoklis
Community Champion
Community Champion

Hello @hixkim 

 

Create a new calculated column with the following formula:

Sorting Column = 
RANKX (
    FILTER ( 'Table', 'Table'[Year-Week] = EARLIER ( 'Table'[Year-Week] ) && 'Table'[Item Number] = EARLIER ( 'Table'[Item Number] ) ),
    'Table'[Unit Cost],
    ,
    ASC,
    DENSE
)

 

Workspace Attached

You're amazing. Thank you!!

selimovd
Super User
Super User

Hey @hixkim ,

 

there is a great article about that from SQLBI. Check it out, I think it will solve your issue:

RANKX on multiple columns with DAX and Power BI - SQLBI

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.