cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
birdie29 Regular Visitor
Regular Visitor

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 Smiley Sad

 

Essentially I want a 'Flexible' ranking of projects by their 'tcv', which on the example on the left table looks like it works fine. However on the right hand table I have filtered by the 'Project. Project Status' to show only 'In Progress' projects and now only the 'In Progress' projects show with the same ranking as on the left hand table.

 

Instead I would like it to show the same as the left table but for 'In Progress' projects ir Rank 1-10. Is this possible?

 

I suspect by 'Rank' DAX is far too simple!

 

Many thanks in advance!

ChrisCapture.PNG

3 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: RANKX Measure Not Filtering As Expected

@birdie29 Try changing ALL to ALLSELECTED Smiley Happy

 

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

Rank =
RANKX ( ALL ( 'Total Projects'[Project.Project ID] ), [tcv],, DESC, SKIP )
v-micsh-msft New Contributor
New Contributor

Re: RANKX Measure Not Filtering As Expected

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 New Contributor
New Contributor

Re: RANKX Measure Not Filtering As Expected

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]:

22.PNG

Regards

4 REPLIES 4
Super User
Super User

Re: RANKX Measure Not Filtering As Expected

@birdie29 Try changing ALL to ALLSELECTED Smiley Happy

 

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

Rank =
RANKX ( ALL ( 'Total Projects'[Project.Project ID] ), [tcv],, DESC, SKIP )
v-micsh-msft New Contributor
New Contributor

Re: RANKX Measure Not Filtering As Expected

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

birdie29 Regular Visitor
Regular Visitor

Re: RANKX Measure Not Filtering As Expected

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)

 

Capture2.PNG

v-micsh-msft New Contributor
New Contributor

Re: RANKX Measure Not Filtering As Expected

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]:

22.PNG

Regards