Forum Discussion

Pallavi's avatar
Pallavi
Icon for Helper II rankHelper II
9 years ago

Rankx

How RankX works in Hierarchy Level?   DB consists of 4 Areas namely North East, North West, South East and South West  i can give a rank for these areas. Each Area consists of different boroughs, s...
Need Help
AlbertoFerrari's avatar
AlbertoFerrari
Icon for Most Valuable Professional rankMost Valuable Professional
9 years ago

Do you need it as a calculated column or as a measure? The code is different. Moreover, if you can provide some sample data, the time to answer is greatly reduced, thus you will obtain a quicker answer :)

 

Anyway, the main idea is that you need to RANKX over both Area and Borrough, using ALLEXCEPT to create the proper filter.

 


Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

Pallavi's avatar
Pallavi
Icon for Helper II rankHelper II
9 years ago

I have used below DAX for Borough

 

Rank At Borough = IF(HASONEVALUE(Table[Area]), RANKX(ALLSELECTED(Table[Borough], [visits],,ASC, Dense), BLANK())

 

i am getting the correct order with in the selected Area if all of the 'Visit' values are unique. If 'Visit' values are repeated numbers are not in order/correct.

 

Is the approach i am using is correct?

 

 

For next Levels (Station and WatchName) i need to consider both Area, Borough for Station and all the three (Area, Borough, Station).

 

Please let me know the correct approach.