Forum Discussion

birdie29's avatar
birdie29
Advocate II
9 years ago
Solved

RANKX Measure Not Filtering As Expected

Hi Everyone   I'm really struggling with a RANKX measure, which I expect is probably simple but I just can't figure it out :(   Essentially I want a 'Flexible' ranking of projects by their 'tcv',...
Need Help
  • Sean's avatar
    Sean
    9 years ago

    birdie29 Try changing ALL to ALLSELECTED :smileyhappy:

     

    EDIT: Actually you may have to use the column...

    Rank =
RANKX ( ALL ( 'Total Projects'[Project.Project ID] ), [tcv],, DESC, SKIP )
  • v-micsh-msft's avatar
    v-micsh-msft
    9 years ago

    Hi birdie29,

     

    As Sean suggested, replace All() with AllSelected () should make it work.

    "

    The ALLSELECTED function gets the context that represents all rows and columns in the query, while keeping explicit filters and contexts other than row and column filters. This function can be used to obtain visual totals in queries.

    "

    Which means Visuals would work on the data which has been selected in the Visual.

    Reference: ALLSELECTED Function (DAX)

    In addition, please also take a look at the following blog, regarding RankX:

    DAX RANKX function scenarios

    Regards

  • v-micsh-msft's avatar
    v-micsh-msft
    9 years ago

    Hi birdie29,

     

    If you would like to select 'Project.Project Status' and have the rankX to work with it, then we might need to add the 'Project.Project Status' into the ALL function.

    For the Rank 2, to hide the rows which have no value in [Tcv], take use of the Visual Level filter to remove the blank values of [tcv]:

    Regards

birdie29's avatar
birdie29
Advocate II
9 years ago

Hi Sean and v-micsh-msft

 

Thanks both very much for your support, using ALLSELECTED seemed to do the trick!

 

I was intrigued by what referencing the column would do as well but that didn't bring the result I was hoping for.

 

In the below screenshot I'm sharing the results of each of the formula I tested, Rank 2 being the one that worked best indicated by the first column of tables as only 10 projects are appearing with no ties. Interestingly if I filter by only projects with the 'Project.Project Status' of 'In Progress', all the different 'Rank' formulas give me the same result.

 

Unfortunately I do have one more issue. If you look at the far right table using Rank 2, I have filtered it by the 'Reporting Entity'; 'Central Reporting', however as there are only 3 projects I would expect to just see those 3 however it adds many more below that with no 'tcv'. Does anyone know why and how to prevent this?

 

Thank you very much!

Chris

 

Rank = RANKX(ALL('Total Projects'[Project.Project ID]),[tcv],,DESC,skip)

Rank 2 = RANKX(ALLSELECTED('Total Projects'),[tcv],,DESC,Skip)

Rank 3 = RANKX(all('Total Projects'[Project.Project ID]),[tcv],,DESC,Skip)

 

v-micsh-msft's avatar
v-micsh-msft
Microsoft Employee
9 years ago

Hi birdie29,

 

If you would like to select 'Project.Project Status' and have the rankX to work with it, then we might need to add the 'Project.Project Status' into the ALL function.

For the Rank 2, to hide the rows which have no value in [Tcv], take use of the Visual Level filter to remove the blank values of [tcv]:

Regards