cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AlexDantin
Helper I
Helper I

Does Power BI support sorting established in a data model?

We have several date dimensions in our data model. Within them, we have hierarchies created to flow from date to month to quarter to year. To avoid the alpha sort on Month and Quarter, we have enforced sorting within the model based on some hidden support columns, but when I add "Quarter Year" to a Power BI report, instead of showing Q1 2015, Q2 2015, Q3 2015 etc, it is showing Q1 2014, Q1 2015, Q1 2016 etc.

 

Since Power BI doesn't currently support grouped or stacked axis like Excel, how do I overcome this?

1 ACCEPTED SOLUTION

Ok - after reviewing the tabular model, the sort is set correctly (and I am exploring the model in Desktop)

 

BUT -

 

Power BI doesn't seem to recognize hierarchies established in a tabular model. The sort order was set to support the hierarchy, not the attribute on its own, so we have to change the sort key.

 

Closing this topic.

 

View solution in original post

11 REPLIES 11
andre
Memorable Member
Memorable Member

Power BI supports Sort By columns.  Click on the column you are trying to manage then click on Modeling->Sort By Column and pick the "hidden" sort column to enforce the desired sort order

I'm directly connected to an SSAS tabular model...I don't see "modeling" as an option??

andre
Memorable Member
Memorable Member

@AlexDantin if you are connecting to the tabular instance, then whoever built that instance for you can add that sort by logic to the date dimension in Visual Studio.  He/She will need to click on the column and then pick Sort By Column in Properties window on the right

We've done that and Power BI is ignoring it - that's why my question was phrased as "established in a data model"

andre
Memorable Member
Memorable Member

sorry, I misunderstood your question.  The Sort By columns seem to work fine for me in Desktop (I just tried itagain just to make sure against an AS model in explore mode).  Are you saying that when you connect to your tabular model using Excel, sorting works, but when you are connecting to it using Desktop, sorting does not work?

Correct. When I connect to the model in Excel, the date attribute is sorted per how it is designed in the model (in this case, Q# YYYY). In desktop Power BI, while I can "sort by" other dimensions selected for a particular visualization (like "Net Amt" or "# of whatever") it does not list the members in the same order (it just alpha sorts so all the Q1s across multiple years come first, then the Q2s, etc)

Are you certain that you are using Desktop and not Designer? I am using Desktop and went through the following excercise:

 

Imported the following data:

Name,Quarter,QuarterSort
Thing1,Q1 2015,2015Q1
Thing2,Q2 2015,2015Q2
Thing3,Q3 2015,2015Q3
Thing4,Q4 2015,2015Q4
Thing5,Q1 2014,2014Q1
Thing6,Q2 2014,2014Q2
Thing7,Q3 2014,2014Q3
Thing8,Q4 2014,2014Q4

 

things1.png

 

Note that there is NO visual indicator of sorting here but I clicked the "Quarter" column heading and it sorts this way (perhaps this is the issue??)

 

Went back to data model, selected the Quarter column and changed Sort by column to QuarterSort and then went back to my report and it looked like this (without me touching anything):

 

things2.png


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

yes, sorting works for me too... If you explore your Tabular model and not import from in it should work.

Ok - after reviewing the tabular model, the sort is set correctly (and I am exploring the model in Desktop)

 

BUT -

 

Power BI doesn't seem to recognize hierarchies established in a tabular model. The sort order was set to support the hierarchy, not the attribute on its own, so we have to change the sort key.

 

Closing this topic.

 

Greg_Deckler
Super User
Super User

Power BI Desktop does support a "Group By Column", (whoops, typo, meant "Sort By Column") so you could use that and have a sort by column like "2015Q1, 2015Q2". You could also change your column information to be 2015 Q1, 2015 Q2, etc and then it should sort the way you want.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

When we've already gone through the effort of designing the model based on the users desires for labels and formatting, renaming all the values doesn't seem like a reasonable alternative.

 

And that still wouldn't solve the issue of displaying Monthly data over more than one year. Power BI recognizes Month name, but lumps all months across years. Not everything should be done as clustered bar/column charts.

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors