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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
MarkPHarris1
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,

 

Mark

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

2 REPLIES 2
smpa01
Super User
Super User

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

let
    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"})
in
    #"Expanded ad"

 

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

Capture.PNG

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Carousel June 2024

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

2
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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