0

I created a measure that will rank universities based on the total score they obtained from multiple projects. This ranking is dynamic based on the applied filters such as Year and Journal. Suppose a user filters for a specific university using the slicer. In that case, it will impact the rank as it is calculated based on the current filter context, including the university selection. However, I want to show the rank of a selected university against all universities (regardless of any other applied filters). For example, if UNI X ranks based on selected years and journals are 32, I want this rank to show up after users filter for UNI X and see the number 32. My issue is once I filter for the particular university to see its rank, the rank will be changed to another number which is not accurate. Here is my DAX code:

NewRank = RANKX ( ALL('SinaA'[University]), CALCULATE (SUM('SinaA'[Score]), ALLEXCEPT(SinaA, SinaA[University],SinaA[Journal], SinaA[Year])), , DESC, Dense )

Here is the ranking link:

https://app.powerbi.com/view?r=eyJrIjoiMDJiZjIwMDctMWJhMS00ZjBjLTljYWMtNWY3NjdkYzM4YmRhIiwidCI6ImUwNWI2YjNmLTE5ODAtNGIyNC04NjM3LTU4MDc3MWY0NGRlZSIsImMiOjN9

The following helps you to better understand the issue:

Use the link above and filter for the first 2 journals (European Journal of Marketing and industrial marketing management), then select the years 2018 and 2019

You will see the ranking table will show up. According to the table, Cardiff University's rank is 6. But when I filter for Cardiff University, using the university slicer, the rank will change to 4, which is not accurate, I want to see 6

Sample Data: https://wichitaedu-my.sharepoint.com/:x:/g/personal/z829w854_wichita_edu/EbUX4mTGpcFCpsm-ROXjuRkBhgryPF-Q4q-DtCehnKw6yQ?rtime=GWxgtq5j20g

Thanks

I tried ALLSELECTED instead of ALL and it returned RANK 1 for all universities.

  • You may benefit from a star-schema for your model. This will make slicing and applying appropriate contexts a lot easier. https://learn.microsoft.com/en-us/power-bi/guidance/star-schema – Luke_0 Jun 02 '23 at 22:00

0 Answers0