Issues sorting one column by another when using filters

[Chris3y]

Hi,

Very new to PBI so the solution here, may be very basic. For reference, I'm using a Direct Connection to a local SQL db. 

I have a table which is something like the following:

Primary Key -  ReportLayoutID - Row Number - Title

0                  -  3245                  -  1                    - Sales

1                  -  3245                  -  2                    - Marketing

2                  -  3245                  -  3                    - IT

3                  -  3248                  -  1                    - Whatever

4                  -  3248                  -  2                    - Not important

5                  -  3248                  -  3                    - Etc

So, my report is concerned with the first 3 rows. I want to display tabular data (in a table or matrix) and show the "Title" column.

However, that column must be sorted by the row number. 

Now when I try to do that, I'm advised "There can't be more that one value in "Row Number" for the sane value in "Title". Please chose a different column for sorting on or update data in "Row Number". 

That makes sense, as the number sequencing does start again in that column as you can see. So I went to "Edit Queries" and applied a filter on the ReportLayoutID column, to only show those with an ID of "3245". I've manually checked the remaining rows and there's no duplicates after the filter was applied. Yet, when trying to sort by the Row Number field, I STILL get the same message!

What am I missing?

[v-cherch-msft · Accepted solution]

Hi @Chris3y

Based on my test, you may check the 'Title' column. The error is because it has duplicate values. If there is no duplicate titles it will not have error and it could be sort by row number as below pictures.

Error

correct

Regards,

Cherie

[Chris3y]

Thank you for the reply.

I have some blanks in that column so I'm sure that's the problem.

Really, all I want to do is display the Title column (among others) in a tabular visual but sorted by the RowNumber one. However, I don't want to display the row number.

If I *do* display rownumber, I can sort the who set in the visual by it but as I say, I'd rather not do that if possible. Can you suggest anything further?

I've tried sorting the whole set by RowNumber column in the query editor but it has no effect when adding just the title column which is then sorted alphabetically. 

[v-cherch-msft]

Hi @Chris3y

Do you use table visual or others? It could be sorted without RowNumber column.

Regards,

Cherie

[Chris3y]

@v-cherch-msft

Rightly or wrongly, I'm specifically using the built-in "matrix" visual. The "Sort by Column" button is greyed out, even after selecting the "RowNumber" column from the field list. The button is only enabled if I add the RowNumber column to the visual which I'm trying to avoid. 

[v-cherch-msft]

Hi @Chris3y

Please follow below steps: 1. Click the 'Title' column 2. Click the 'Sort by column' and select 'RowNumber' column.

Regards,

Cherie

[Chris3y]

That's what I've been trying all along. I tried again got the same message:

"There can't be more than one value in "RowNumber" for the same value in "Title". Please chose a differenct column for sorting.

I cannot understand why it's so difficult to sort by a non-visible field. To be clear, if I had the row number field to the Matrix, I can sort it without issue, simply by clicking its column heading. 

[v-cherch-msft]

Hi @Chris3y

Could you share the .pbix file or the sample data for your scenario? You can upload it to OneDrive and post the link here or send me via private message. Do mask sensitive data before uploading.

Regards,

Cherie