RANKX always returns 1

[olimilo]

I've already inspected the other threads with the same dilemma, but most of them are running with SUMs of a numeric column. I'm trying to get the rank of an item through the number of instances it has appeared on the table (ie: COUNTROWS). Using the formula below, I always get a 1 on my Rank.

I'm pretty sure I've no problems with the [# of Violations] not having a CALCULATE since it's already a calculated measure, which seems to be the cause of the problem most of the posts for RANKX. I'd like to point out that I'm also using Page-level filters, unsure if this will affect the measure in any way.

' Measure
# of Violations = COUNTROWS(Violations)

' Measure
Rank = 
    RANKX(
        ALLSELECTED('Violations'),
        [# of Violations], , DESC, Dense
    )

Below is the expected result from the sample data:

Violation	2017	Rank	2018	Rank
A	6	1	7	2
B	4	3	8	1
C	5	2	5	3

Site	Violation	Inspected	Year Inspected
A1	A	1/1/2017	2017
A1	B	1/1/2017	2017
A2	C	1/1/2017	2017
A3	A	1/1/2017	2017
A1	B	1/1/2017	2017
A2	C	1/1/2017	2017
A3	A	1/1/2017	2017
A1	B	1/1/2017	2017
A2	C	1/1/2017	2017
A3	A	1/1/2017	2017
A1	B	1/1/2017	2017
A2	C	1/1/2017	2017
A3	C	1/1/2017	2017
A1	A	1/1/2017	2017
A3	A	1/1/2017	2017
A1	A	1/1/2018	2018
A2	B	1/1/2018	2018
A3	C	1/1/2018	2018
A1	A	1/1/2018	2018
A1	B	1/1/2018	2018
A2	C	1/1/2018	2018
A3	A	1/1/2018	2018
A1	B	1/1/2018	2018
A3	C	1/1/2018	2018
A1	A	1/1/2018	2018
A2	B	1/1/2018	2018
A1	C	1/1/2018	2018
A1	A	1/1/2018	2018
A2	B	1/1/2018	2018
A3	B	1/1/2018	2018
A1	B	1/1/2018	2018
A2	A	1/1/2018	2018
A3	A	1/1/2018	2018
A1	B	1/1/2018	2018
A2	C	1/1/2018	2018

Would like to know how to solve this. Any input is definitely appreciated.

[Ashish_Mathur]

Hi,

This too should work

=RANKX ( ALL ( Violations[Violation] ), [# of Violations],, DESC, DENSE )

I dont think the ALLSELECTED function is required.

[mattbrice]

Please try replacing your ALLSELECTED statement with this

ALLSELECTED('Violations'[Violation] )

[olimilo]

Thanks for the help everyone! Although I find it weird that I had to indicate the column I'm going to be working on. I thought that using it on the measure would be enough.

[Zubair_Muhammad · Accepted solution]

Hi @olimilo

A tiny change in your formula is needed

Rank =
RANKX ( ALLSELECTED ( Violations[Violation] ), [# of Violations],, DESC, DENSE )

[Zubair_Muhammad]

@olimilo