Ability to create a visualization to Sort by one column then another

[krueb]

I have a details data set

State	Priority
Release Ready	2
In Progress	1
Release Ready	1
In Progress	4
Sprint Ready	3
Release Ready	1
Sprint Ready	1

I have a separate table with a relationship to the details table by State

State	State Sort Order
Release Ready	1
In Progress	2
Sprint Ready	3

In a visualization I want to be able to show the States in State Sort Order Ascending (without displaying the State Sort Order column) to the end user, then, by the Priority Order Ascending

State	Priority
Release Ready	1
Release Ready	1
Release Ready	2
In Progress	1
In Progress	4
Sprint Ready	1
Sprint Ready	3

[v-lili6-msft · Accepted solution]

hi @krueb 

For your case, you could try this way:

Step1:

You need to create a relationship between data table and separate sort table by state column

Step2:

In data view for separate sort table, Selected state column then choose sort by column->State Sort Order

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column

Step3:

When create visual, just use state field from this separate sort table instead of data table.

Result:

and here is sampel pbix file, please try it.

Regards,

Lin

[krueb]

It didn't work the way I thought it would, I can sort by the state order, but when I try and sort by the priority so I can see all the number 1 priorites in state order, it just sort by the prioirty regardless of the state . . .

[amitchandak]

@krueb , you can choose one sort column and mark it as sort column

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

[mahoneypat]

In Data View for your table with 3 rows, highlight the State column and then on Column Tools above choose Sort By Column, and choose your State Sort Order column.  Also see this link for more details.  You should also then be able to sort by Priority in your visuals.

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat