Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
birdie29
Helper II
Helper II

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', 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
Sean
Community Champion
Community Champion

@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 )

View solution in original post

v-micsh-msft
Employee
Employee

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

View solution in original post

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

View solution in original post

4 REPLIES 4
v-micsh-msft
Employee
Employee

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

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

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

Sean
Community Champion
Community Champion

@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 )

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.