Forum Discussion
Ordering Columns
In Query Editor, bring up your query with the raw data. From the menu, select "Add Column" and then "Conditional Column". In the dialog, you can give this a name like DeptSort and define the conditions. Just keep adding rules until you have all of your values covered. Here is an example of what you will end up with (this is the code that will be written for you):
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRJLUgsKslNzStR0lEyMlCK1YlWikIRNAaL+aKImZiABSNRBM2MlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Departments = _t, Incidents = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Departments", type text}, {"Incidents", Int64.Type}}), #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "DeptSort", each if [Departments] = "ADepartment" then 4 else if [Departments] = "ZDepartment" then 2 else if [Departments] = "MDepartment" then 1 else if [Departments] = "YDepartment" then 3 else 0) in #"Added Conditional Column"After that, you can use Sort By in the Data Model.
You can create a custom column like this
departmentsorting = switch(true(), [department] = "IT Service Operations","1", [department] = "IT Applications & Infrastucture","2", [department] = "IT Infrastructure & Service Transformation","3", [department] = "Projects & Programmes","4")
Update:
Now, create a summarized table with all columns including custom column, now select Department coumn and sort by departmentsorting. Now, you will see the Columns in the Chart as you needed.
rocky09- Using DAX like that will not work, I tried that prior to answering. The reason is that DAX is going to whine and complain that:
"This column can't be sorted by a column that is already sorted, directly or indirectly, by this column"
DAX REALLY hates circular references.
This is why you need to do it in the Query Editor instead.
You are correct. I didn't realized that until you mentioned it.