Dynamic sorting for matrix column headers

[jsangerman]

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

Program	Project	Milestone	Finish
Program 1	Project A	Milestone 3	4/1/2020
Program 1	Project A	Milestone 2	5/1/2020
Program 1	Project A	Milestone 1	6/1/2020
Program 1	Project B	Milestone 2	7/1/2022
Program 1	Project B	Milestone 3	8/1/2022
Program 1	Project B	Milestone 1	9/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?

[Vmpbi]

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 · Accepted solution]

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.

[jsangerman]

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

[lbendlin]

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