cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ReynoldsTstan16
Post Patron
Post Patron

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
Microsoft
Microsoft

@ReynoldsTstan16

 

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

23 REPLIES 23
ben-lehman
Advocate I
Advocate I

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.

@ben-lehman 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. 

mskern
Regular Visitor

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
Microsoft
Microsoft

@ReynoldsTstan16

 

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

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? 

This solution is incorrect

Is there a way to implement this when in Direct Query mode? I had it working in import mode, but now the needs of my project have required direct queries

I don't think this exists as of yet.  I have been searching for a while. I even created my query to sort so that data would come into the query editor window in the proper chronologic order like this, 

 

  order by case when [Month] like '%Jan%' then 1
					   when [Month] like '%Feb%' then 2
					   when [Month] like '%Mar%' then 3
					   when [Month] like '%Apr%' then 4
					   when [Month] like '%May%' then 5
					   when [Month] like '%Jun%' then 6
					   when [Month] like '%Jul%' then 7
					   when [Month] like '%Aug%' then 8
					   when [Month] like '%Sep%' then 9
					   when [Month] like '%Oct%' then 10
					   when [Month] like '%Nov%' then 11
					   when [Month] like '%Dec%' then 12
					   end 

Still nada! If this isn't a suggestion for an improvement, I don't know what is. Smiley Tongue

Jcole429
Frequent Visitor

I solved it by sorting by another column that I called date order

@Jcole429

 

Hey I agree there are work arounds.  I solved it the same way.  Most things MS are Drag and Drop and I am hoping to usher that same motif into life here. 

 

Thank you for a great suggestion!  

Thanks. This worked out perfectly.

Thanks Herbert...this should help quite a bit!

 

Tim

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors