March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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:
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.
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
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!
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.
@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.
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.
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_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).
[LOB] is a field/column from a SQL table.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
84 | |
82 | |
74 | |
49 |
User | Count |
---|---|
160 | |
144 | |
103 | |
74 | |
57 |