Sorting row or column values in a specific order

[Anonymous]

Hi, this is a 2-part question.

Part #1:

I followed a previously published solution: https://community.powerbi.com/t5/Desktop/Sorting-rows-and-columns-in-matrix-visual/m-p/723838#M34934...

to try to sort a field in a matrix row. The field name is "LOB" and the sort column field I created is "Sort_LOB" where:

Sort_LOB = IF([LOB] ="Accounts",1

,IF([LOB] ="ESC Accounts",2

,IF([LOB] ="Billing",3

,IF([LOB] ="ESC Billing",4

,IF([LOB] ="Tech",5

,IF([LOB] ="ESC Tech & Hardware",6))))))

However, when I then selected the field I want to sort, i.e. "LOB" and then went to "Sort by Column", then selected the new field "Sort_LOB" as the sort by field, nothing changed. Meaning, the row values were still sorted alphabetically as opposed to in the order I specified in the DAX for field name Sort_LOB. See screenshot. 

Part #2:

In the same matrix, I also display a column field with 2 values - Coaching and Pre-Coaching.

As you can see from the screenshot -

the 2 values are displayed in alphabetical order, "C" then "P".

But I really want it to display Pre-Coaching, then Coaching.

How do I do that? Do I also create a new field with sort order specified like in the DAX for LOB, which that one didn't seem to work in the visualization!

Thanks.

[v-lili6-msft]

hi  @Anonymous 

You need to add two conditional column in edit queries as below:

Then use sort by column in power bi desktop the row and column.

https://radacad.com/sort-by-column-in-power-bi

Result:

and here is my sample pbix file, please try it.

Regards,

Lin

[Anonymous]

Thanks, but I have one question though.

LOB is from one table, and 'Type' is from a different table.

That would mean I can't do conditional column, right?
Or, if I can, how do I do that?
Thank you!

[mjantune]

Part #1:

Is the "Sort_LOB" field a calculated column? If it is, you should use sort by column in the Data View 

You select the "LOB" column and you sort by the Sort_LOB column.

Instead of using IF, its better to use SWITCH.

Sort_LOB = SWITCH( [LOB] ,
"Accounts", 1 ,
"ESC Accounts", 2 ,
"Billing" , 3 ,
"ESC Billing" , 4 ,
"Tech" , 5 ,
"ESC Tech & Hardware" , 6 ,
7
)
--- 7 is for any other values

Part 2:

Here is a post explaining how to sort by custom orders.
http://www.excelnaccess.com/custom-sorting-in-power-bi/

You could use the same method as in the row order.

I hope this helps.

[Anonymous]

Re. Part #1

I changed the Sort_LOB calculated column to the SWITCH dax, then I selected LOB and sorted by Sort_LOB, and encountered and error message - screenshot attached.

[amitchandak]

@Anonymous , Create a new column

Lob Name = [LOB]

and use this and also set a sort column on this. You can not use a derived column as sort column for the base column

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

[Anonymous]

So I did this. I created a new column LOBName = LOB then changed Sort_LOB to be based on LOBName and not LOB.

Then I created the visual using LOB but sorted based on LOBName but it still generated the circular dependency error.

[mjantune]

is [Lob] a measure or a Column?

You could also create a new table (Sort_Lob) to do the sorting.

LOB	Sort_LOB
Accounts	1
Esc Accounts	2
Billing	3
Esc billing	4
Tech	5
Esc Tech & hardware	6

Then you relate this table to the Employee_Team table you want to sort, and use this Sort_Lob table in the report.

[Anonymous]

Per your suggestion, I created a new table (Sort_LOB) with 2 columns:

LOB
Sort_LOB

Then I mapped the relationship between Employee table and this new table using the field 'LOB'.

For this solution, would I then put the LOB field and Sort_LOB fields (from new table) in the visualization, and then sort by Sort_LOB?
Because I did that, and LOB still wasn't sorted accordingly.

Thanks.

[mjantune]

---

@Anonymous wrote:

Per your suggestion, I created a new table (Sort_LOB) with 2 columns:

LOB
Sort_LOB

Then I mapped the relationship between Employee table and this new table using the field 'LOB'.

For this solution, would I then put the LOB field and Sort_LOB fields (from new table) in the visualization, and then sort by Sort_LOB?
Because I did that, and LOB still wasn't sorted accordingly.

Thanks.

---

Can you send an screenshot or something? Because that has always worked for me in the past. Also, in the new table you could sort the LOB field by the Sort_LOB field, and then just use the LOB field from the new table in the visualization, also check if its sort descending or sort ascending (just in case).

[Anonymous]

[LOB] is a field/column from a SQL table.