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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Matrix Column Head Order

Matrix Column Heads.jpg

 

Is there a way to change the order of the column heads in a Matrix table.  Currently it displays in alphabetical ascending order.  I would like to reverse that if possible.  A prefered option would to be able to put it in a custom order.  Are either of these options available?

1 ACCEPTED SOLUTION
v-haibl-msft
Employee
Employee

@Anonymous

 

We can do that with following steps. Assuming we have a table like below. We can add a reverse index.

Matrix Column Head Order_1.jpgMatrix Column Head Order_2.jpg

 

Close and apply Query Editor, select the Type column and make it sort by column of Index.

Matrix Column Head Order_3.jpg

 

Now the order of the column heads will follow the Index.

Matrix Column Head Order_4.jpg

 

If you want to put it in a custom order, you can customize the index yourself. For example, you can create a conditional column in Query Editor.

Matrix Column Head Order_5.jpgMatrix Column Head Order_6.jpg

 

Best Regards,

Herbert

View solution in original post

30 REPLIES 30
lavanaya_kapoor
New Member

lavanaya_kapoor_0-1680629560150.png

 

need help in arranging this in order 0-5 <6-15 days< 15-30 days< 30+ days as this is the bucket group based on data. 

HugoJesus
Helper IV
Helper IV

Hey everyone, 

Someone can help me out with this topic??
Re: Matrix Sort Column - Microsoft Power BI Community

Best Regard's

Hugo Jesus

Anonymous
Not applicable

Thank you 

Anonymous
Not applicable

I went about this a different way, seems effective so far.

 

I have my three main regions "AMERICAS", "EMEA" and "APAC".

 

The sort keeps trying to put them in:
AMERICAS
APAC
EMEA

 

I want:

AMERICAS
EMEA
APAC

 

I tried the table method with sorting, but that broke some of my measures.

 

Here is what I did:

I built my matrix table, then I clicked on the "Columns" dropdown, then clicked "New Group":

 

From there, I created one group per value - Important here: Name the groups with the index prefix ( ie. 1:, 2:, 3: 😞
Once complete, the columns come in with the prefixed index. This will sort based on the index you specify in the grouping.

 

This is nice and clean because it doesn't affect your data for filtering, grouping, etc, and is pretty simple to implement.

Thanks its work like a magic 

Perfect! Thanks so much! 🙂

Thank you! I worked very well for me.

@Anonymous Maybe this will help.  Create an index column like this:

 

Ordercol = if('tablename'[region] = "AMERICAS", 1, 

                  if('tablename'[region] = "EMEA", 2, 3)).

 

This order column then can be treated like an indicies.  Now make sure that the column is a number so go to modeling and change the data type to whole number.  

 

Then select the region field and and make sure it is highlighted in the field list.  Next, go to modeling and then in the banner, look for 'Sort' and choose 'Sort by Column' and this time select Ordercol as your sort order.    Please let me know if this doesn't work. 

This results in a Circular Dependency.

Anonymous
Not applicable

For this topic- ok. Because, here uses a Matrix with a Table, where it showed how to reorganize the Table. But when do you want to sort by a measure and columns? (example: structure of a Dimension containing only "measures" separately). How to create multiple sorts by measures and columns? (% accumulated order descending + State + Name + etc

MarcoG2000
Frequent Visitor

Hi, another alternative would be to use the IF function in Dax.

 

Example

Order = If([Reitailer] =”A”;1;

        If([Reitailer] =”B”;2;3))


After it applies the sorting of the Reitailer column with the Order column.

This solution is incorrect as well. PowerBi highlights in red the function

rohampourmehr
Frequent Visitor

Quick follow up question on this topic:

 

This works perfectly in the Desktop version, however the Sort Order is automatically overideen to the deafult once uploaded to the Online Workspace. Any Solutions?
 

v-haibl-msft
Employee
Employee

@Anonymous

 

We can do that with following steps. Assuming we have a table like below. We can add a reverse index.

Matrix Column Head Order_1.jpgMatrix Column Head Order_2.jpg

 

Close and apply Query Editor, select the Type column and make it sort by column of Index.

Matrix Column Head Order_3.jpg

 

Now the order of the column heads will follow the Index.

Matrix Column Head Order_4.jpg

 

If you want to put it in a custom order, you can customize the index yourself. For example, you can create a conditional column in Query Editor.

Matrix Column Head Order_5.jpgMatrix Column Head Order_6.jpg

 

Best Regards,

Herbert

It's the little things, worked like a charm!

Anonymous
Not applicable

Hi,
I had a similar issue.
interestingly it is working for one Matrix and not for Another.
I have recheck each and everything. 

Any suggestions

Anonymous
Not applicable

Hi,
I had a similar issue.
interestingly it is working for one Matrix and not for Another.
I have recheck each and everything. 

Any suggestions

@Anonymous  

There seems to be a bug in the order system. The way around it is to make sure that you have numbers as numbers, text as text, and dates as dates.  Then make sure that you click on the field list on the right side.  click on the field you want to be sorted.  Then click off the field, but onto another field in the same table of the field list. 

 

Then click back on the field and then the modeling should show up as sort by column.  When it does, choose the field you want to sort the field by.  So for example maybe you want to sort your organization by date. Click on org then off of org then back on to org. Then int he modeling tab at the top, the Sort by Column button will be available.  Click on that and then chose date.  This will sort your org by date.  Hope this helps. 

Anonymous
Not applicable

Hi 

Thanks @S184019 

I am able to achieve it.

But facing same issue for Horizontal Slicer visual.

I am also not able to get help with the Sort Option the visual provides.

I need to sort by Index which I have mentioned.

Could you suggest.

So the challenge is on how to sort the values in slicer?  If so, I found this: 

 

 

  1. Create a new table (in query editor) that has a column of distinct values that you are using in your slicer.
  2. Add a numeric column using whatever logic you need based on the desired order.
  3. Close and Apply to load this new table to data model, then create a relationship to your fact table.
  4. Click on the new field of distinct values, then click on the Modeling tab and select Sort by Column.  Choose the numeric column that you created to provide the order.
  5. Use this distinct list in your slicer.

Did this help? 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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