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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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