Ranking

[bswank31]

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

[parry2k]

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

[bswank31]

alright. Thanks so much for your help. It is greatly appreciated! 

[parry2k]

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

[bswank31]

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? 

[parry2k]

@bswank31 see attached file, there is a Rank measure and column, you want to use Rank Measure to work with slicers. Tweak the solution as you see fit.

[bswank31]

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? 

[parry2k]

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 😃

[bswank31]

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!! 

[parry2k]

@bswank31 also does each row will always be unique? 

[bswank31]

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. 

[parry2k]

@bswank31 I know this is what you are looking for, just did this but have a question. Do you want to rank GXT over all the records correct? It is not ranked by date or by another grouping except by the special logic for a category.

[bswank31]

Yes. I want to rank by overall GXT and but I need to have a year, month and site slicer. 

[lbendlin]

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?

[bswank31]

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

RETURN

IF( 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! 

[lbendlin]

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?

[bswank31]

What I am looking for is to create a Ranking from my GXT column and if my category column Contains "Including" to give it the same rank as the interval it belongs with: See how each color belongs in the same interval?

This first table works and is what I want but when I apply it to my table with more sites and years (See below table) it gets all messed.

This table is not filtering correctly when i select the year and the ranking is not working. 

I hope this makes more sense.

thanks,

[lbendlin]

Nowhere in your source data does it say "interval" .  What is an interval?  Is it a combination of Site, Project and Hole?

[bswank31]

Sorry. I forgot to add that in. The "interval" I am talking about is the From (m) column and To (m) columns. The From (m) to To (m) is an interval. 

[lbendlin]

You have overlapping intervals. 

that makes it impossible to determine which one "belongs" to which other one.

[bswank31]

Yes. The overlapping intervals are the "Including" intervals. 

This table below is working correctly but when I apply the code to a bigger data set is when it doesn't work properly.

Ranking =

VAR current_category = SELECTEDVALUE( Sheet1[Category])

VAR GXT = SELECTEDVALUE(Sheet1[GXT])

RETURN

IF( current_category = "Including",

    CALCULATE( RANK.EQ( GXT, Sheet1[GXT], DESC), FILTER( ALLEXCEPT(Sheet1, Sheet1[Date]), Sheet1[Category] = "Including")),

    CALCULATE( RANK.EQ( GXT, Sheet1[GXT], DESC), FILTER( ALLEXCEPT(Sheet1, Sheet1[Date]), Sheet1[Category] <> "Including")))

I just don't understand why it would work for this smaller data set and not a bigger one with a year filter and more data?