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
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!
Chris
Solved! Go to Solution.
@birdie29 Try changing ALL to ALLSELECTED
EDIT: Actually you may have to use the column...
Rank = RANKX ( ALL ( 'Total Projects'[Project.Project ID] ), [tcv],, DESC, SKIP )
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:
Regards
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
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:
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)
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 Try changing ALL to ALLSELECTED
EDIT: Actually you may have to use the column...
Rank = RANKX ( ALL ( 'Total Projects'[Project.Project ID] ), [tcv],, DESC, SKIP )
User | Count |
---|---|
160 | |
111 | |
96 | |
86 | |
75 |
User | Count |
---|---|
158 | |
136 | |
133 | |
81 | |
61 |