Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I
Helper I

Sorting Matrix by Columns with Measure when connecting to a Dataset

Hi There,


I'm currently making a matrix in powerbi of dates compared to a category value, which when in a matrix looks like the below:


Name    a                     b                    c                   d

Task1    01/01/2019     01/01/2019   01/01/2019   05/01/2019 

task2 etc.


the problem is I need the ordering to be more list this:


Name    a                     d                    c                   b

Task1    01/01/2019     05/01/2019   01/01/2019   01/01/2019 

Task2 etc.


My problem is that I am using a dataset which means I have no access to change the sortbycolumn on the field with a,b,c,d or add a new calculated column. Does anyone have a workaround to be able to do this? Preferably without using the new composite model functionality.


Thanks for your help,



Not applicable

No workaround is possible as far as I know. The sorting has to come from source

View solution in original post

Super User
Super User

@MarkPHarris1  you can create a sortOrder like this in your dataset

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjFU0lEyMNQHIiMDQ0sgx9DAQClWB5uMEU4ZYxQZUyQZE5iMEboeU5wyZjhlzFFkkO2xAMnEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Task = _t, Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Task", type text}, {"Date", type date}, {"Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Task", "Date"}, {{"ad", each _, type table [Task=nullable text, Date=nullable date, Value=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let x = [ad], y =  Table.Sort(x,{{"Value", Order.Descending}}), z = Table.AddIndexColumn(y, "Index",1,1) in z),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Task", "Date", "Value", "Index"}, {"Task", "Date", "Value", "Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Index] = 1)),
    #"Grouped Rows1" = Table.Group(#"Filtered Rows", {"Task"}, {{"ad", each _, type table [Task=text, Date=date, Value=number, Index=number]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "Custom", each let x = [ad], y = Table.AddIndexColumn(x,"sortOrder",1,1) in y),
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom1",{"Custom"}),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Custom", {"Task", "Date", "Value", "Index", "sortOrder"}, {"Task", "Date", "Value", "Index", "sortOrder"}),
    Custom1 = Table.SelectRows(#"Expanded Custom", each ([Index] <> 1)),
    #"Merged Queries" = Table.NestedJoin(Custom1, {"Task"}, Table.Group(#"Expanded Custom1", {"Task"}, {{"maxSortOrder", each List.Max([sortOrder]), type number}}), {"Task"}, "Table (3)", JoinKind.LeftOuter),
    #"Expanded Table (3)" = Table.ExpandTableColumn(#"Merged Queries", "Table (3)", {"maxSortOrder"}, {"maxSortOrder"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table (3)",{"Index"}),
    #"Grouped Rows2" = Table.Group(#"Removed Columns", {"Task"}, {{"ad", each _, type table [Task=text, Date=date, Value=number, maxSortOrder=nullable number]}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows2", "Custom", each let x = [ad], y=List.Max(x[maxSortOrder])+1, z = Table.AddIndexColumn(x,"sortOrder",y,1) in z),
    #"Removed Other Columns2" = Table.SelectColumns(#"Added Custom2",{"Custom"}),
    #"Expanded Custom2" = Table.ExpandTableColumn(#"Removed Other Columns2", "Custom", {"Task", "Date", "Value", "maxSortOrder", "sortOrder"}, {"Task", "Date", "Value", "maxSortOrder", "sortOrder"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom1",{"Index"})&Table.RemoveColumns(#"Expanded Custom2",{"maxSortOrder"}),
    #"Grouped Rows3" = Table.Group(#"Removed Columns1", {"Task"}, {{"ad", each _, type table [Task=text, Date=date, Value=number, sortOrder=number]}}),
    #"Removed Other Columns3" = Table.SelectColumns(#"Grouped Rows3",{"ad"}),
    #"Expanded ad" = Table.ExpandTableColumn(#"Removed Other Columns3", "ad", {"Task", "Date", "Value", "sortOrder"}, {"Task", "Date", "Value", "sortOrder"})
    #"Expanded ad"


and enforce the sortOrder in the matrix as following to come to the desired


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

Proud to be a Super User!

New Animated Dashboard: Sales Calendar

Not applicable

No workaround is possible as far as I know. The sorting has to come from source

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors