Matrix Sorting Columns

[hejszyszkiiii]

Hello,

I am having matrix table within power bi that :

Has Year-Quarter as columns,

Country, Property as row,

Two measures as a value.


The problem I have is that the values are represented like below:

Q1 KPI, Q1 KPI2, Q2 KPI, Q2 KPI2 (and so on)

What I would like to achieve is like below:

Q1 KPI, Q2 KPI, Q1 KPI2, Q2 KPI2.

Anyone has an idea how to achieve that?

Thanks in advance!

[Sahir_Maharaj · Accepted solution]

Hello @hejszyszkiiii,

Can you please try this approach:

SortingTable = 
DATATABLE(
    "Quarter", STRING,
    "KPI Order", INTEGER,
    {
        {"Q1 KPI", 1},
        {"Q2 KPI", 2},
        {"Q3 KPI", 3},
        {"Q4 KPI", 4},
        {"Q1 KPI2", 5},
        {"Q2 KPI2", 6},
        {"Q3 KPI2", 7},
        {"Q4 KPI2", 8}
    }
)

[ACS_BIM]

I have read and tried just about every option in the forums for sorting the column headers in a Matrix visual in Power Bi. Not one has been successful, so let me try to explain as I am sure I am missing something.

I connected to our SQL database and used the "Import" option then selected the tables I needed. Verified relationships.

I have built a report and used the OOTB Matric visual all the data is showing what I need in the visual 

The column headers are coming from one data point in one of the tables.  No matter  what I do they are showing left to right alphabetically.  I need to reorder the columns to a specific order.

Table column headers by default:

I need these to appear from left to right per the following order:

1. Connection Location
2. Connection Type
3. Connection Size
4. Pressure
5. Flow
6. Temperature
7. Amps
8. Breaker
9. Voltage
10. Phase
11. Notes

I tried adding a column to the table, set it to "Sort" but have yet to figure out teh DAX expression to accomplish this.

I added a table with a sort order and mapped the relationship - but to no avail.

Some have said, you can drag a column header left or right to reorder - to no avail.

Is the issue because it is coming from a connected database?

Is this simply not possible (which would seem odd)?

Please help!!

[BIswajit_Das · Accepted solution]

Hello @hejszyszkiiii 
In a matrix Visual if you want to sort a column you can do it this way;
For example;

let's be the demo table with column

data

Q1 KPI

Q1KPI2

Q2 KPI

Q2KPI2

Now inorder to get the order right you have to create an another Order column like

data	ORDER
Q1 KPI	1
Q1KPI2	3
Q2 KPI	2
Q2KPI2	4

then you can sort the column as per the order column like mentioned below

Initially

After sorting the column with Order column

Thanks & Regards...

[Sahir_Maharaj · Accepted solution]

Hello @hejszyszkiiii,

Can you please try this approach:

SortingTable = 
DATATABLE(
    "Quarter", STRING,
    "KPI Order", INTEGER,
    {
        {"Q1 KPI", 1},
        {"Q2 KPI", 2},
        {"Q3 KPI", 3},
        {"Q4 KPI", 4},
        {"Q1 KPI2", 5},
        {"Q2 KPI2", 6},
        {"Q3 KPI2", 7},
        {"Q4 KPI2", 8}
    }
)