Multi level custom sort

[thmonte]

I have a database that looks like the following:

Name	Rank	Promotion Date	Promotion Number
Person A	I	1/1/2017	1
Person B	I	1/1/2016	1
Person C	II	1/10/2015	1
Person D	II	1/10/2015	2
Person E	II	10/10/2001	1
Person F	III	10/1/2015	5
Person G	III	10/1/2014	5

I am creating a table visual in my report and I would like to have a multi-level sort on this visual and I can't seem to figure it out.  I also want a dynamic number in the visual for each row that is pretty much a "global" rank based on the filter.  My report will never be ran with multiple "Rank" selected but it could have multiple Promotion Dates.  If I were to do the sort in Excel this is what it would look like.

So after would the full table would like similar to below.  Now if I were to filter out just Promotion Date = 1/10/2015, Person C would turn into Visual Number 1 and and Person D would have Visual Number 2

Name	Rank	Promotion Date	Promotion Number	Visual Number
Person B	I	1/1/2016	1	1
Person A	I	1/1/2017	1	2
Person E	II	10/10/2001	1	1
Person C	II	1/10/2015	1	2
Person D	II	1/10/2015	2	3
Person G	III	10/1/2014	5	1
Person F	III	10/1/2015	5	2

I've tried RANKX but I don't believe its doing the sorts properly.  Following this thread: http://community.powerbi.com/t5/Desktop/How-to-Rank-a-list-based-on-2-values-double-rankX/m-p/44076#...

Any help would be appreciated!  Thanks!

[Eric_Zhang · Accepted solution]

---

@thmonte wrote:

I have a database that looks like the following:

Name	Rank	Promotion Date	Promotion Number
Person A	I	1/1/2017	1
Person B	I	1/1/2016	1
Person C	II	1/10/2015	1
Person D	II	1/10/2015	2
Person E	II	10/10/2001	1
Person F	III	10/1/2015	5
Person G	III	10/1/2014	5

I am creating a table visual in my report and I would like to have a multi-level sort on this visual and I can't seem to figure it out.  I also want a dynamic number in the visual for each row that is pretty much a "global" rank based on the filter.  My report will never be ran with multiple "Rank" selected but it could have multiple Promotion Dates.  If I were to do the sort in Excel this is what it would look like.

So after would the full table would like similar to below.  Now if I were to filter out just Promotion Date = 1/10/2015, Person C would turn into Visual Number 1 and and Person D would have Visual Number 2

Name	Rank	Promotion Date	Promotion Number	Visual Number
Person B	I	1/1/2016	1	1
Person A	I	1/1/2017	1	2
Person E	II	10/10/2001	1	1
Person C	II	1/10/2015	1	2
Person D	II	1/10/2015	2	3
Person G	III	10/1/2014	5	1
Person F	III	10/1/2015	5	2

I've tried RANKX but I don't believe its doing the sorts properly.  Following this thread: http://community.powerbi.com/t5/Desktop/How-to-Rank-a-list-based-on-2-values-double-rankX/m-p/44076#...

Any help would be appreciated!  Thanks!

---

@thmonte

The functions RANKX does't support order by multiple columns, as a workaround, you can try to create a calculated column as below and then rank sorting by that column

sortColumn =
LEFT ( CONCATENATE ( yourTable[Rank], "HHHHHHHHH" ), 10 ) & "-"
    & FORMAT ( yourTable[Promotion Date], "YYYY-MM-DD" )
    & "-"
    & RIGHT ( CONCATENATE ( "000", yourTable[Promotion Number] ), 4 )

Then create a measure as below so that you can sort by that measure to get the expected sorting order.

rankOverAll = RANKX(ALLSELECTED(yourTable),CALCULATE(MAX(yourTable[sortColumn])),,ASC,Dense)

For the visual number, you can create a measure as 

Visual Number = 
RANKX (
    FILTER(ALLSELECTED(yourTable),yourTable[Rank]=MAX(yourTable[Rank])),
    CALCULATE ( MAX ( yourTable[sortColumn]) ),
    ,
    ASC,
    DENSE
)

See more details in the attached pbix file.

[thmonte]

What's with the H's in the concatenation?

Either way it did work!  Thanks for the help..

I just used your first measure in my table visual and it worked dynamically.  Whats the difference between the two measures?

[Eric_Zhang]

---

@thmonte wrote:

What's with the H's in the concatenation?

Either way it did work!  Thanks for the help..

I just used your first measure in my table visual and it worked dynamically.  Whats the difference between the two measures?

---

I use H's because H is before I in alphabet order, it is for sorting purpose.  The difference of the two measures is, the rankOverAll ranks dynamically in overall ranks(I + II + III..) while Visual Number ranks dynamically in individual ranks(I, II, III...).

[thmonte]

Also if I was to put another column in my table called "Location" of these people and then filter the visual multi-selecting locations and it dynamically updated as I selected the locations in the filter would I need to change anything in the DAX?

[thmonte]

I didn't think to ever concatenate the fields to one column and use RANKX - Does RANKX just use alphabetical sorting for this?