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
Niki
Helper III
Helper III

matrix column header sort DESC!!

Hi all,

 

Going crazy! So hope any one can help.

I have a column with dates which are the matrix column series. I want the most recent date to show up first, but it seems microsoft is recognizing the data and automatically sorts it ASC, as there is no option to sort the columnheaders (which is odd right?) Im not getting it fixed. I'll give some extra info on why and also why most of the workarounds do not work for me.

 

First of all, I am working with a tabular model, so al kinds of sorting and calculating is done in there and not possible in PBI desktop.

The table these dates are in is a datetable, which has every date in it starting 1-1-2018 till far in the future. When loading it into my model I restrict it with maximum date today. The column I need in the matrix columnheader is some kind of a settlementdate, which is calculated before it comes into the model and will always give the last day of the month, except in the current month it will give yesterdays date. So every date in March 2019 will have settlementdate 31-3-2019.

 

So as we speak the table has every date from 1-1-2018 till 8-4-2019 and all those days have their settlement date (end of month), which for April 2019 is 7-4-2019.

 

Solutions I have tried:

- Sorting the table in tabular --> Is not picked up by PowerBI

- Sort by another column  in tabular (created a calculated column which is similar to the settlementdate column and sorted it by the settlementdate column) --> Is not picked up by PowerBI, or it is being picked up but still sorts it ASC (by that other column)

- created an index column (rankx) and tried to sort the settlementdate column by the index column (in tabular) --> not possible because it creates some kind of circular dependency.

- solution of manually adding an index is not an option as the data is growing over time and is variable.

 

Any one a solution on how to do this? P
lease keep in mind, solution will probably need to be in the tabular model.

 

Kind regards,

Niki

 

 

1 ACCEPTED SOLUTION
AkhilAshok
Solution Sage
Solution Sage

What is your data source? If it is SQL, then you could add a Rank column while importing the data to tabular model:

Rank() over(order by settlementdate DESC) AS DateSort 

Afterwards, you could sort Date column by this DateSort column.

View solution in original post

5 REPLIES 5
AkhilAshok
Solution Sage
Solution Sage

What is your data source? If it is SQL, then you could add a Rank column while importing the data to tabular model:

Rank() over(order by settlementdate DESC) AS DateSort 

Afterwards, you could sort Date column by this DateSort column.

@AkhilAshok 

Thanks! it works 🙂

v-frfei-msft
Community Support
Community Support

Hi @Niki ,

 

Please check the thrid- party bolg to find the solution. If it doesn't meet your requirement, kindly share your sample data and excepted result to me. Please upload your files to One Drive and share the link here.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
mussaenda
Super User
Super User

I haven't tried to sort the column header but maybe you can sort you table desc then add an index column in power query then sort you date column by index

Hi,

 

That does not work because it will give an error on circular dependency (first creating an index column based on column A and then try to sort column A by that index column..).

Also sorting from tabular is not kept in the power BI report, it recognizes the date format and puts it ASC, no matter how I formatted the original table in the model.

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.