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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
jsangerman
Helper I
Helper I

Dynamic sorting for matrix column headers

I have a table of project milestones that looks something like this:

 

ProgramProjectMilestoneFinish
Program 1Project AMilestone 34/1/2020
Program 1Project AMilestone 25/1/2020
Program 1Project AMilestone 16/1/2020
Program 1Project BMilestone 27/1/2022
Program 1Project BMilestone 38/1/2022
Program 1Project BMilestone 19/1/2022

 

This would be repeated with Program 2 and a new set of Projects.

 

I'm creating a page where only 1 program is shown at a time, and a matrix that shows Projects in rows and Milestones in columns. For each program, I want the column headers to be sorted by the earliest (MIN) Finish date. So, for the program shown, I want them shown Milestone 3 | Milestone 2 | Milestone 1 from left to right.

 

Without other input, the Milestone column headers are sorted alphabetically. However, that doesn't work for the program shown, and each program could have a different order. I was thinking of creating a table to define the sort order for each program, but then learned that DAX tables can't take an input from the user (e.g., by filtered value).

 

Is there another way to define a sort for a subset of the data using a filtered defined by the user?

1 ACCEPTED SOLUTION
danextian
Super User
Super User

Hi @jsangerman ,

 

Columns cannot be custom sorted by using a measure - a physical column, calculated or created in PQ is necessary. Also, a unique item in a column cannot be sorted more than once  (eg Milestone 1 is either in the first or second order but not both).  As a workaround, you can create another column that references the original and append an invisible string to it. The count of which depends on the rank of the column value you were going to initially use for sorting. Please see attached sample pbix.

danextian_0-1709616974239.png

danextian_1-1709616987149.png

 

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

4 REPLIES 4
Vmpbi
Regular Visitor

Thanks for sharing… check out the links below. It provides a comprehensive guide on how to sort measures as columns 

in a matrix visual. 

https://insurancedatainsights.blog/2024/04/27/dynamic-data-display-matrix/ 

https://www.youtube.com/watch?v=k5FMfvPmkv4&t=190s 

danextian
Super User
Super User

Hi @jsangerman ,

 

Columns cannot be custom sorted by using a measure - a physical column, calculated or created in PQ is necessary. Also, a unique item in a column cannot be sorted more than once  (eg Milestone 1 is either in the first or second order but not both).  As a workaround, you can create another column that references the original and append an invisible string to it. The count of which depends on the rank of the column value you were going to initially use for sorting. Please see attached sample pbix.

danextian_0-1709616974239.png

danextian_1-1709616987149.png

 

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Yes! It occurred to me this would work, and then I saw your post confirming it. Thank you for the example, as well.

lbendlin
Super User
Super User

You can only sort a column by itself or by another column.  Not by a measure.

 

If this is important to you please consider voting for an existing idea or raising a new one at https://ideas.fabric.microsoft.com/?forum=2d80fd4a-16cb-4189-896b-e0dac5e08b41

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.