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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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