Ordering Columns

[Anonymous]

I have a bar graph with various columns and I'd like to order them in a very specific way:

IT Service Operations

IT Applications & Infrastucture

IT Infrastructure & Service Transformation

Projects & Programmes

Could someone help me out?

[Greg_Deckler]

So, are these values in single column or multiple columns?

If they are values in a single column then you would create a calculated column to assign a number to each one and use a Sort By column.

If they are different columns, you just order them in the Axis the way you want them.

If those two options don't work, I don't understand your issue and you need to provide more detail.

[Anonymous]

This is a single column value which I have created a grouping for (Departments).

[Greg_Deckler]

OK, in your M code, you are going to need to add a custom column (conditional column) that specifies a number for each of your Departments that is in the order you want them sorted. So, 1, 2, 3, 4. Once you have that, then in your Data Model, you can specify the "Sort By" column for your Departments column and then they will sort in the order that you want them.

[Anonymous]

I'm sorry, fairly new to all of this. M Code?

[Greg_Deckler · Accepted solution]

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.

[rocky09]

@Anonymous

M is nothing but your Query editor (in simple)

check my above updated answer.

[rocky09]

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.

[Greg_Deckler]

@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.

[rocky09]

@Greg_Deckler

You are correct. I didn't realized that until you mentioned it.

[rocky09]

can you post sample data?