Ranking by a specific column but keeping certain rows in order with specific criteria

[bswank31]

Hi all! I am trying to Rank by GXT (Highest to lowest) but if category has "Including" for that interval then keep the row that is "including" with the interval it belongs with no matter the ranking. Below is a table before ranking. Maybe Rank the GXT column but don't rank the GXT's that the Category has "Including" to keep them in order? I am not sure how to make this work. Thanks!

Your help is much appreciated!

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

[kameronyork]

So sorry for not including this in the original response!
To see rows in the table that have no information there are two options:
1.  You can replace the blank output in the IF() statment with a zero.  That will result in data being in those rows and will thus be shown.  Here is the updated 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",
    0,
    // Ignore any rows that say "Including".
    CALCULATE( RANK.EQ( gxt, Sheet1[GXT], DESC), FILTER( ALL(Sheet1), 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.

2. Select "Show Items with no Data" for the Category column of the table.  Here is an image of how to do that:

Either way will result in the desired output for the table!

I hope this is what you are looking for!  Thanks for letting me help out 🙂

[bswank31]

Thank you. I got it to match your table. I do have one more question. Is there a way to get the "Including" rows to be right after the rows they should follow? So could we ignore what the Including GXT number is for "Including" so it will be in order with the correct interval it belongs too?

[kameronyork]

I'm not fully sure I understand the question, but I hope this is close to the result you would like:
You can sort the table by more than one column by holding shift and selecting the column headers.  This is the result I achieved by first sorting the table ASC by Hole and then DESC by the From amount.  I think the result you are hoping for will be possible with this approach as it seems the rows with arrows between them have either From or To values that are the same.  

[bswank31]

That works to get the includings where they belong. But now I just need the Rankings to be in order (1-9) to show the best GXT, but also have the includings to be in their correct spots.  Is there a way to keep the GXT number for the "Including" rows to not be included in the GXT Rank order? 

Again, I greatly appreciate your help!

[kameronyork]

I believe I have a solution!  If I am understanding correctly you would like two rankings to be done, first for rows that are not marked "Including" and then for rows that are marked including.  Resulting in the following table:

I replaced the first section of the IF statement with another rank that is specifically for the "Including" rows.  That way the highest "Including" is marked as one as well.

Here is 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( ALL(Sheet1), Sheet1[Category] = "Including")),
    // Ignore any rows that say "Including".
    CALCULATE( RANK.EQ( gxt, Sheet1[GXT], DESC), FILTER( ALL(Sheet1), 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.

Then sort by the rank column!

I apologize for my misunderstandings.  If this solved your problem please mark it as the solution! Thank you for working with me on this 🙂

[bswank31]

Thanks! This has worked for my example table we have been working with but when I apply this to my bigger table that has dates from 2023 and 2022 it doesn't seem to work correctly. I am not sure why there would be any thing different.

The filters that I have on my canvas is a Year, Month and Site slicer. When I select what year I want to see, the table filters but when I sort my rank column it still has both years showing and things are not correct.  I would like to be able to select what year and only see those lines for that year selected. It doesn't seem to be ranking correctly either. When I filter by ranking it is not ranking the highest GXT and not grouping the includings where they should go. Sorry this became a bigger issue. I greatly appreciate your help though. It is so close!

[kameronyork]

This is because of the ALL statement we were using!  The ALL statement will remove any filters applied to a table in order to evaluate the measure for all of the data.  As you would like to run a filter on the Date column it would be best to use the ALLEXCEPT statement to tell the measure to allow filtering to be done to the Date column.  Here the the new and updated 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.

Attached are screenshots showing the results of this measure using a year slicer.  I copied the original data but changed the years to 2022 to make this demonstration.
Unfiltered:

2022:

2023:

If you would like to ensure there is only one year showing at a time I would highly suggest changing the slicer settings to require a single selection.  This will make sure there is always a year selected and will never show every year in the table.

If this has been helpful kudos would be much appreciated 🙂  Thanks!

[bswank31]

I applied the new and updated code and its closer. The ranking is not quite right tough. This snip is one site : 

This is all sites selected. 

Thanks!

[kameronyork]

Adding the site column to the the ALLEXCEPT statement will fix this 🙂 Anytime you would like to use a column in a slicer simply add that column to the ALLEXCEPT statement and things should work! 🙂

Here is the updated code:

IF( current_category = "Including",
    CALCULATE( RANK.EQ( gxt, Sheet1[GXT], DESC), FILTER( ALLEXCEPT(Sheet1, Sheet1[Date], Sheet1[Site]), Sheet1[Category] = "Including")),
    // Ignore any rows that say "Including".
    CALCULATE( RANK.EQ( gxt, Sheet1[GXT], DESC), FILTER( ALLEXCEPT(Sheet1, Sheet1[Date], Sheet1[Site]), 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.

Hopefully this response has been helpful!  If this is everything you need solved please mark this thread as the solution 🙂

[bswank31]

I guess I made that confusing. Sorry. I am using the code before you changed the allexcept site. Do you know why my Ranking is not ranking correctly? It doesn't seem to be. Shouldn't it be giving a rank of 1 to the highests GXT and the same rank if there is an including and then move on to 2, 3, 4 rank as the GXT numbers get smaller?

[kameronyork]

My best guess is that it's ranking the GXT values based on a combination of the Site and Hole since it is ignoring the date.  I would recommend adding the Site and the Hole to the ALLEXCEPT statement in the measure and see if the error persists.

[bswank31]

I added Site and Hole to the ALLEXCEPT statment and this is the output:

Ranking 1 =

VAR current_category = SELECTEDVALUE( 'Sheet1 (2)'[Category])

// Gets the value of the category on the current row.

VAR gxt = SELECTEDVALUE('Sheet1 (2)'[GXT])

// Gets the value of GXT on the current row.

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)'[Hole]), 'Sheet1 (2)'[Category] = "Including")),

    // Ignore any rows that say "Including".

    CALCULATE( RANK.EQ( gxt, 'Sheet1 (2)'[GXT], DESC), FILTER( ALLEXCEPT('Sheet1 (2)', 'Sheet1 (2)'[Date], 'Sheet1 (2)'[Site], 'Sheet1 (2)'[Hole]),'Sheet1 (2)'[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.

When I sort by GXT to get the highest at the top it is giving me both years, and the ranking is messed up again. 

I appreciate you helping me with this.

[kameronyork · Accepted solution]

Hello,  I now understand adding additional argurments to the ALLEXCEPT statement will cause the rank to appear based on the combinations of those columns in the table.  For example, if you took the code how you have it now and applied a filter to each slicer you will see the rank count up perfectly.  I am not sure of a way to accomplish what you are looking for.  It might be possible, but I don't have a way to make a measure act as dynamically as you would like it to be.

Please see this solution from a different post for help on this issue: https://community.fabric.microsoft.com/t5/Desktop/Dynamic-measures-with-two-slicers/td-p/3102323

If errors persist I would recommend creating a new thread in the forum and asking for more insights on this issue.  Thanks 🙂

[kameronyork]

This can be accomplished using a measure:

Ranking = 
VAR current_category = SELECTEDVALUE( Data[Category])
// Gets the value of the category on the current row.
VAR gxt = SELECTEDVALUE(Data[GXT])
// Gets the value of GXT on the current row.
RETURN

IF( current_category = "Including",
    BLANK(),
    // Ignore any rows that say "Including".
    CALCULATE( RANK.EQ( gxt, Data[GXT], DESC), FILTER( ALL(Data), Data[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.

This measure will result in the following output in a table:

I hope this helps!  If it does, please mark this as the solution! 🙂

[bswank31]

Thanks for responding so quickly. It is greatly appreciated. I created the measure you gave me but the output I get doesn't show the Includings. They are completely gone. Also, is there a way to get the "Including" rows to be right after the other row they belong with? 

Here is my measure and output:

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",

    BLANK(),

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