Rankx

[Pallavi]

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, suppose say like North East consists of 12 boroughs (a,b,c,d,e,f,g,h,i,j,k,l)now i need to rank the boroughs for this north east area. when i apply rankx method on borough it is considering all boroughs (52 boroughs in these 4 areas) and giving me rank as following

a  4

b  9

c   52

.

.

.

k 23

l 8

but i need with in the selection (ranking boroughs in north east area), it should omit rest of the boroughs of different areas.

Please help me

[AlbertoFerrari]

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]

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.

[Pallavi]

I need it like a calculated measure.

sample data

Below is the hierarchy in my table

Area

Boroughs

Stations

WatchName

i.e Area (NE, NW, SE, SW)

Each Area consists of different Boroughs and each Borough consists of different Stations and each station will have different watches.

There is a 'Visits' column in this table. I need to rank each criteria based on no. of visits from the selection criteria.

For Area i can rank like below

Rank at Area Level = RANKX(ALLSELECTED(Table[Area]), [Visits],, ASC]

Now i need t rank for Boroughs with the particular selected Area, i.e if i select NE, then boroughs within that region need to be ranked.

Similarly for next level ranking on Stations, i need to consider based on the selection criteria, i.e

in NorthWest -> Bexley Borough -> Stations Ranking

for one more level NorthWest -> Bexley Borough -> Hammersmith Staton -> watches ranking.

It is a bit complex while implementing, Could you help me in resolving this

Thanks