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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
TonyEv6
Frequent Visitor

RANK and RANKX when the fact table is filtered by multiple dimensions

Does anyone have experience using the RANK and RANKX DAX functions when filtering across tables? A client wanting to display a list of organisations ranked by a variety of KPIs where the ranking needs to be filtered by up to four different dimensions - date, the selected KPI etc So, for example, we need to change the ranking based on date and the selected KPI.

 

Ranking works fine when the fact table is disconnected but once a dimension is added, a row context is forced and each of the entities gets a rank of 1. Tried various methods and DAX functions to force the measure to ignore row context but without success.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@TonyEv6 , When you add any other column in the visual that is not part of Rank/Rankx, the rank will redistributed or partitioned by the additional column

Refer: Impact of extra visual column on RankX| Why Rank 1 when added city id - https://youtu.be/t77bd57IVqk 

Power BI - New DAX Function: RANK - How It Differs from RANKX: https://youtu.be/TjGkF44VtDo

 

Assume you create a Rank

City Rank = RANKX(all(Geography[City]),[Sales])

 

and add city ID to visual i will give 1 for rank

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

2 REPLIES 2
TonyEv6
Frequent Visitor

Thanks. That makes sense. 

amitchandak
Super User
Super User

@TonyEv6 , When you add any other column in the visual that is not part of Rank/Rankx, the rank will redistributed or partitioned by the additional column

Refer: Impact of extra visual column on RankX| Why Rank 1 when added city id - https://youtu.be/t77bd57IVqk 

Power BI - New DAX Function: RANK - How It Differs from RANKX: https://youtu.be/TjGkF44VtDo

 

Assume you create a Rank

City Rank = RANKX(all(Geography[City]),[Sales])

 

and add city ID to visual i will give 1 for rank

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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