Forum Discussion
Sorting row or column values in a specific order
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.
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.
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.
[LOB] is a field/column from a SQL table.
Per your suggestion, I created a new table (Sort_LOB) with 2 columns:
LOB
Sort_LOBThen 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.
Anonymous wrote:Per your suggestion, I created a new table (Sort_LOB) with 2 columns:
LOB
Sort_LOBThen 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 , 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
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.
