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

[TonyEv6]

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.

[TonyEv6]

Thanks. That makes sense. 

[amitchandak · Accepted solution]

@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