Forum Discussion
Ranking
Hi. Is there a way to Rank a specific column (GXT) but if there is an "Including" in the Category Column then keep those intervals together?
I hope someone can help me. Thanks a bunch!
Example:
| Date | Site | Project | hole | category | From m | To m | GXT |
| 8/14/2023 | BS | Aug | 1 | 17.8 | 21.9 | 10.8 | |
| 8/14/2023 | BS | Aug | 1 | 32 | 34.4 | 7.3 | |
| 8/14/2023 | BS | Aug | 1 | 124.4 | 129.1 | 12.9 | |
| 8/14/2023 | BS | Aug | 3 | 34.1 | 40.2 | 22.7 | |
| 8/14/2023 | BS | Aug | 3 | Including | 37.4 | 40.2 | 17 |
| 8/14/2023 | BS | Aug | 3 | 215.2 | 219.3 | 21.4 | |
| 8/14/2023 | BS | Aug | 4 | 85.3 | 90.9 | 24 | |
| 8/14/2023 | BS | Aug | 4 | Including | 87.3 | 90.9 | 19.8 |
| 8/14/2023 | BS | Aug | 4 | 103.6 | 106.7 | 8.6 | |
| 8/14/2023 | TS | Jul | 1 | 107.4 | 113.1 | 30.8 | |
| 8/14/2023 | TS | Jul | 1 | Including | 107.4 | 110.8 | 23.1 |
| 8/14/2023 | TS | Jul | 2 | 50.9 | 57.3 | 42 | |
| 8/14/2023 | TS | Jul | 2 | Including | 50.9 | 53.3 | 25.2 |
24 Replies
if there is an "Including" in the Category Column then keep those intervals together?It is not clear what you are trying to achieve. What is the expected outcome based on your sample data?
Yes. If category has "Including" then keep that interval with the one it belongs with:
I would like to be able to rank the highests GXT. If there is an "Including" then give it the same rank as the interval it belongs to. Here is the code I am using but it is not quite right:
Ranking 1 =VAR current_category = SELECTEDVALUE( 'Sheet1 (2)'[Category])VAR gxt = SELECTEDVALUE('Sheet1 (2)'[GXT])RETURNIF( current_category = "Including",CALCULATE( RANK.EQ( gxt, 'Sheet1 (2)'[GXT], DESC), FILTER( ALLEXCEPT('Sheet1 (2)', 'Sheet1 (2)'[Date],'Sheet1 (2)'[Site]),'Sheet1 (2)'[Category] = "Including")),CALCULATE( RANK.EQ( gxt, 'Sheet1 (2)'[GXT], DESC), FILTER( ALLEXCEPT('Sheet1 (2)', 'Sheet1 (2)'[Date], 'Sheet1 (2)'[Site]),'Sheet1 (2)'[Category] <> "Including")))(This was from another person helping me)The output isn't putting the includings where they belong and the ranking is all messed up. It also is not filtering correctly when I select my years slicer.Your help is much appreciated!
sorry, still not clear to me, especially what you mean by "the interval it belongs to" - please explain what that means.
What is the expected outcome based on your sample data?
so rank will change based on the slicer selection, correct?
for 2nd answer, it means each row is a unique row. Stay tuned! Will send the solution soon. Going for a run 😃
Ya, I would like to be able to not use a slicer and see what the ranks are and also use the slicers and see the ranks. I Will most like use the year slicer the most but would like the option to use the site and month slicers also. Thanks so much for looking into this!!
bswank31 that's the problem, and I don't expect it will work with different selections in the slicer.
Here is the advice: There are always many ways to solve a problem, but the experts always suggest, following best practices and keeping your DAX simple by doing the heavy lifting on preparing the data and having a good model. Sometimes we go down this rabbit hole of solving a problem by writing a complex DAX which either doesn't perform or does not give the correct result.
So the solution which I gave you is super simple because we have done the work on preparing the data to achieve the goal, it will perform on large data and it is super simple code. Now the choice is yours. Good luck!
alright. Thanks so much for your help. It is greatly appreciated!
Yes. I want to rank by overall GXT and but I need to have a year, month and site slicer.
Each Row will have some of the same data like date, site, hole and project. The From and To columns will have some intervals that are the same but will have "Including" in the Category column which those ones should rank right after the interval that it is included in.
This is close! The only thing I see that isn't working correctly is the "Including" isn't following the correct interval it belongs to (If there is an Including with that interval). Is there a way to give the "Including" the same rank as its interval it goes with?
bswank31 if you noticed it adds a new column in PQ "GTX for Rank" and it is based on the assumption that "Including" row is next to the row it belongs to, basically it is a way to group otherwise there no logical way to find out "including" belongs to which group. If there is another way to do that then that would be the logic for "GTX for Rank", you have to work with your data and determine what logic works best.
I had someone helping me with this a few days ago and this code gave me what I needed until I applied it to my larger data source. Below is the code and output of the code.
Ranking = VAR current_category = SELECTEDVALUE( Sheet1[Category]) // Gets the value of the category on the current row. VAR gxt = SELECTEDVALUE(Sheet1[GXT]) // Gets the value of GXT on the current row. RETURN IF( current_category = "Including", CALCULATE( RANK.EQ( gxt, Sheet1[GXT], DESC), FILTER( ALLEXCEPT(Sheet1, Sheet1[Date]), Sheet1[Category] = "Including")), // Ignore any rows that say "Including". CALCULATE( RANK.EQ( gxt, Sheet1[GXT], DESC), FILTER( ALLEXCEPT(Sheet1, Sheet1[Date]), Sheet1[Category] <> "Including"))) // Using the ALL() function to ensure the whole table is condsidered when evaluating each row. // Uning FILTER() to ensure "Including" is being excluded.Is there a way to get any of this logic into what you gave me to make it work so the ranking will work how I want?
