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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
bswank31
Helper II
Helper II

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

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!

 

DateSiteProjectholecategoryFrom mTo mGXT
8/14/2023BSAug1 17.821.910.8
8/14/2023BSAug1 3234.47.3
8/14/2023BSAug1 124.4129.112.9
8/14/2023BSAug3 34.140.222.7
8/14/2023BSAug3Including37.440.217
8/14/2023BSAug3 215.2219.321.4
8/14/2023BSAug4 85.390.924
8/14/2023BSAug4Including87.390.919.8
8/14/2023BSAug4 103.6106.78.6
8/14/2023TSJul1 107.4113.130.8
8/14/2023TSJul1Including107.4110.823.1
8/14/2023TSJul2 50.957.342
8/14/2023TSJul2Including50.953.325.2

 

1 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 🙂

View solution in original post

15 REPLIES 15
kameronyork
Resolver I
Resolver I

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:
p.png

 

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 🙂

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?

bswank31_0-1692116380970.png

 

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.  
p2.png

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!

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:
p3.png

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 🙂

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!

bswank31_0-1692125398952.png

 

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:
unfiltered.png
2022:
2022.png
2023:

2023.png

 

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!

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

bswank31_0-1692133255303.png

This is all sites selected. 

bswank31_1-1692133393862.png

Thanks!

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 🙂

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?

bswank31_0-1692134277353.png

 

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.

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. 
bswank31_0-1692135933909.png

I appreciate you helping me with this.

 

 

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
Resolver I
Resolver I

This can be accomplished using a measure:
p.png

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:
p2.png
I hope this helps!  If it does, please mark this as the solution! 🙂

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")))
bswank31_0-1692113403926.png

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.