Forum Discussion
Helper IIRankx
Most Valuable ProfessionalDo 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
Helper III 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