sort values in number column based on text column in Table View

[riffraff55]

Im new to Power BI and learning as I go along however the below has baffled me a little bit (maybe im just overthinking it! but would appreciate some guidance) I have a dataset with a number of columns. column 1 is a text field which has names of countries. column 2 is a number field that contains years in the format 'yyyy' which spans from the last 20 years. so for every country there is associated 20 rows (each row for each year) e.g.

Column 1    Column 2

England       1999
England       1996

England        2001
England        1995

I can sort column 1 in ascending order but how can i then sort column 2 in ascending year order. There will always be duplicates in column 1 (so the country will be repeated 20 times - each for every year).

im in the Table view and want to sort the order as I need to then do a DAX calculation based on GDP but looking at previous year etc.

[Greg_Deckler]

@riffraff55 Not sure I completely understand your dilemma but if you click the ellipses for a visual then you can select Sort axis and then what to sort by.

[riffraff55]

hi, thanks for responding.

when i try to do a sort on column 1 in ascending order thats fine. the data is sorted in ascending order. however i need to do a second sort on the column 2 which holds the years value but when i try to do a sort on this column - although it sorts it (from 1994 - 2024) it messes up column 1 and the countries are not in ascending order.

how can i keep the sort in ascending on column1 and also sort on column 2 without it affected column 1? does that make more sense?

[Greg_Deckler]

@riffraff55 You could create a calculated column in PQ or DAX. In DAX it would be:

Sort Column = [Country] & "|" & [Year]

Add this column to your table visual. Turn off word wrap, shrink the column to nothing. Sort by this column.

[MNedix · Accepted solution]

It does make sense but not where you want to. I am not aware of a "Sort by and then by" function in PowerBI in Table view. I know it exists in other programs but not in PowerBI. However, if you create a Table visual then the sorting will be done automatically (You sort column 1 and then 2 will be sorted by itself).