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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Power BI Desktop Error in RankX: Starting at 2 instead of 1 randomly

So. I have been working on this for longer then I would care to admit, but I can't seem to find an answer. I have what I think to be a simple endevor; to rank all of the rows in my data by their ROW_ID. Now, since all of my rows are already listed in order from 1-n, theorectically, it should just relist the rows in the same order, like the below example shows:

Correct-Refresh - Copy.PNG

The problem is that sometimes, I would say 90% of the time, when I refresh the data it instead is calculated and displayed like this:

Wrong-Refresh.PNG

As you can see, it is starting at 2 instead of 1, randomly, for seemingly no reason. This behavior throws off other calculations I wish to do on the data, and I am not sure why it is acting this way.

 

Here is the Rank Measure: 

Rank = RANKX (ALLSELECTED(TEST_TABLE), CALCULATE(SUM(TEST_TABLE[ROW_ID])), , ASC, Dense)

 

I am somewhat new to Power BI, and am unsure if this behavior is a bug, or if there is something I am missing. Any insights into what is happening would be much appreciated!

Thank you.

 

7 REPLIES 7
Anonymous
Not applicable

From more testing the rankx functions only seems to be going wrong when one of the columns, which I will call Description (a column filled with strings) is included in the data. More specifically when the column is included in a visual, it also seems to throw off some visual filters I have on the table.

For example, I have a filter that looks to the measure Flag (Flag = IF([Rank]<='TopN'[TopN Value],1,0)), which basically sets it to filter the data by which rows have Flag = 1. This enables the user to change the "What if Parameter" TopN, thus filtering the table by their TopN value. When the Description column is included, the visual correctly shows that in rows where Rank < TopN Value, Flag is 1, but is also shows all of the rows where Flag is 0

So where it should show this (Where top N is 2):

Rank---ROW_ID---LASTUPDATE---FLAG---DESCRIPTION

1 1 6/19/2020 1 "random string text"

2 2 6/19/2020 1 "random string text2"

It is instead showing:

Rank---ROW_ID---LASTUPDATE---FLAG---DESCRIPTION

1 1 6/19/2020 1 "random string text"

2 2 6/19/2020 1 "random string text2"

3 3 6/19/2020 0 "random string text3"

4 4 6/19/2020 0 "random string text4"

etc...

I am still not sure what is causign these issues, as again when Description is included, sometimes it will show correctly, but more often then not it wont.

v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

If any of answers slove your problem, please feel free to accept it as a solution.

If not, please provide details below:

Do you have any blank cells in the table or in original data?

Could you share a little data so i can reproduce this problem?

 

Best Regrads

Maggie

 

Anonymous
Not applicable

There are no blank cells in the table or original data. Unfortunetly, due to the sensitive nature of the data I am unable to share it. I can try to describe a few of the columns in the table.

 

LASTUPDATE: Date/time field.

Type: 6 different values (A, B, C, D, E, F)

Text: Different lines of text "Apples are read", "Grapes are purple," etc

Date1: Date/time field

Date2: Date/time field

 

Only thing of note to me is that in LASTUPDATE and the Type columns there are a lot of similar values. Maybe that has something to do with it?

parry2k
Super User
Super User

@Anonymous if you sort it by Rank do you see there is rank 1 assigned to a different row, maybe we are not seeing the full picture here

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.

 



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.

Anonymous
Not applicable

When I try sorting the lsit by rank I am unable to find 1 at any point in the list.

Anonymous
Not applicable

Hi @Anonymous, Instead of ALLSELECTED(TEST_TABLE) inside RANKX, try to use ALLSELECTED(TEST_Table[ROW_ID])

Anonymous
Not applicable

When I try the rank measure that you suggest not only does it still start at 2 instead of 1, but the data is also greatly misordered.

For some reason I can't post photos like the ones above, but here is a sample of what I am seeing (note this is partly down the list):

 

36---6/10/2020 12:01:04 AM ---107766---0

37---6/10/2020 12:01:04 AM ---107767---0

2---6/10/2020 12:01:04 AM ---107696---0

3---6/10/2020 12:01:04 AM ---107697---0

4---6/10/2020 12:01:04 AM ---107698---0

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.