RANKX fails after filtering

[Anonymous]

Hi all

I have come across a RANKX issue. I basically want to rank locations by a score from a measure, a very simple thing which works for DESC rankings and also works for ASC rankings if nothing is filtered. However, when I filter the whole page to remove some locations, the ASC Ranking only starts at 2 (probably beacuse the filtered locations get blanks?)

Here's the Measure:

Turnover Rank = RANKX(
ALLSELECTED('PowerBI_DCMAdmin ALL_Dim_Location'[SiteCode]);[Annualized Staff Turnover];;ASC;Dense)

It all works fine when showing all locations (no filter on the page):

But when I filter for certain locations, the rank now starts only at 2:

Anybody know what's going wrong?

Thanks for the help!

[amitchandak · Accepted solution]

@Anonymous , first check do you have blank location or a location is missing in the master/dimesnion table. Address that first.

Filter blank from address and check

Try like

Turnover Rank = RANKX(
ALLSELECTED('PowerBI_DCMAdmin ALL_Dim_Location'[SiteCode]);calculate([Annualized Staff Turnover],not(isblank('PowerBI_DCMAdmin ALL_Dim_Location'[SiteCode])));;ASC;Dense)

[Anonymous]

Hi @PaulDBrown @amitchandak 

Thanks for the quick guidance! I was aware of the blanks possibly causing issues, but could not find a way to filter them out, this has helped!

BUT technically speaking, if a SiteCode was to have a Blank Annualized Turnover, it should get rank 1. But there is no site that is blank... so the lowest value should get rank 1...unless I am simply not able to see some blank sites. Weird, huh?

[PaulDBrown]

@Anonymous 

One way of forcing a value is by simply "adding 0" to your base measure. For example say your 

[Annualized Staff Turnover] were:

Annualized Staff Turnover = SUM(table[staff turnover])

You can force a value (and avoid blanks) with:

Annualized Staff Turnover = SUM(table[staff turnover]) + 0

[PaulDBrown · Accepted solution]

@Anonymous 

The BLANKS may well be the culprits.

Try:

Turnover Rank =
RANKX (
    FILTER (
        ALLSELECTED ( 'PowerBI_DCMAdmin ALL_Dim_Location'[SiteCode] );
        NOT ( ISBLANK ( [Annualized Staff Turnover] ) )
    );
    [Annualized Staff Turnover];
    ;
    ASC;
    DENSE
)

[amitchandak · Accepted solution]

@Anonymous , first check do you have blank location or a location is missing in the master/dimesnion table. Address that first.

Filter blank from address and check

Try like

Turnover Rank = RANKX(
ALLSELECTED('PowerBI_DCMAdmin ALL_Dim_Location'[SiteCode]);calculate([Annualized Staff Turnover],not(isblank('PowerBI_DCMAdmin ALL_Dim_Location'[SiteCode])));;ASC;Dense)