cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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
Highlighted
Helper II
Helper II

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

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

Highlighted
Super User VII
Super User VII

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

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

 






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.





Highlighted
Community Support
Community Support

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

Hi @rbenson 

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

 

Highlighted
Frequent Visitor

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

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

Highlighted
Frequent Visitor

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

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

Highlighted
Frequent Visitor

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

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?

Highlighted
Frequent Visitor

Re: Power BI Desktop error in RankX: From 2 instead of 1 random

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.

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors