Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Sorting row or column values in a specific order

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. Capture.JPG
 

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 -

Capture.JPG

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.

 

10 REPLIES 10
v-lili6-msft
Community Support
Community Support

hi  @Anonymous 

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

4.JPG5.JPG

 

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

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

 

Result:

6.JPG

 

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

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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
Regular Visitor

Part #1:

 

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

Captura Sort By.PNG

 

 

 

 

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
Not applicable

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. Capture.JPG

@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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

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.

is [Lob] a measure or a Column?

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

Captura Enter data.PNG

 

 

 

 

LOBSort_LOB
Accounts1
Esc Accounts2
Billing3
Esc billing4
Tech5
Esc Tech & hardware6

 

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

Anonymous
Not applicable

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.


@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
Not applicable

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.