Rankx issue - tables not directly related

[Anonymous]

Hi All,

The test PowerBI file for this specific issue is attached here: 201010 Test Rank. 

The issue is rankx where the tables are not directly related.

I have a model with a fact table and a summary table that rolls up countries and cities; and country and city slicer tables. The model looks like this:

The issue is rankx where the tables are not directly related.

I have a model with a fact table and a summary table that rolls up countries and cities; and country and city slicer tables. The model looks like this:

I have set the model up this way as in the real data I have multiple fact tables and the need for both a country and a city slicer both with an overall result for all countries or cities. I have tried to demonstrate the slicer setup in the test dashboard that looks like this:

I am struggling with the rank function using the countries or the cities table. It is returning a 1 regardless of what I try.

I can't create a standalone summarized table as there are a number of other dynamic slicers on the dashboard. 

I have tried every combination of rankx with summarize that I can come up with and I keep getting 1. 

I would really appreciate any help. 

[lbendlin · Accepted solution]

The ranking issue is the least of your problems.  You will want to reconsider your data model.

With very few exceptions cities do not exist in multiple countries.  You should use a single geography hierarchy with a single filter direction  countries>cities>facts

You can still use separate tables for countries and cities if you want but you can also put them in the same table.  The slicers don't care if there are other columns in the slicer source table.

[Anonymous]

Thanks @lbendlin. I pondered over this for quite a while and your suggestion of using a single geographic hierarchy is of course the right way to go. The model is cleaner and I can still have an 'All' record in there for each now that I have redesigned the table.

Thanks for your time in responding and encouraging me to think more deeply about the data model. Greatly appreciated.