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

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:

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
24 REPLIES 24
parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

parry2k
Super User
Super User

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

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.

bswank31_0-1692293238048.png

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
Super User
Super User

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

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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_0-1692291320225.png

 

parry2k
Super User
Super User

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 😃



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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
Super User
Super User

@bswank31 also does each row will always be unique? 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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. 

bswank31_0-1692278621019.png

 

parry2k
Super User
Super User

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

 

parry2k_0-1692231556841.png

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

lbendlin
Super User
Super User

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:

bswank31_0-1692192745805.png

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.
bswank31_3-1692193141337.png

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?

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.

bswank31_0-1692219633464.png

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

bswank31_1-1692219809482.png

I hope this makes more sense.

thanks,

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

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. 

bswank31_0-1692221986962.png

 

You have overlapping intervals. 

lbendlin_0-1692223129259.png

 

 

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

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?
bswank31_0-1692223382532.png

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.