cancel
Showing results for
Did you mean:

# TRANSPOSE

Super User
439 Views
Super User

## TRANSPOSE

Excel has a TRANSPOSE function, DAX does not. After creating these DAX quick measures I think I know why. And yes, I realize that this is a single click in Query Editor but if you abosolutely have to have a dynamic table/matrix transpose, here you go. I have included transpositions for everything from a 1 row, 2 column table to a 4 row, 4 column table. Additional transpositions can be created based upon the pattern presented here.

For reference, here is the 4x4 transposition.

```Transposed4x4 =
// We must build our row one column at a time, each varialbe is named __rowRC were R=row and C=column
// We start by building our first row "set" using all of the values from the first Column of the original table
// There will be as many of these row sets as there are columns in the original table.
// There will be as many values in each row set as there are rows in the original table.

VAR __row1 = FILTER('Table4x4',[Index]=1)
VAR __row2 = FILTER('Table4x4',[Index]=2)
VAR __row3 = FILTER('Table4x4',[Index]=3)
VAR __row4 = FILTER('Table4x4',[Index]=4)

VAR __row11 =
SELECTCOLUMNS(
GROUPBY(
__row1,                                 // We first filter our table to a single row
[Index],                                // Group by row
"1",MAXX(CURRENTGROUP(),[Column1])      // Get the MAX of the first Column and put it in a column named "1"
),
"Row","1"                                   // Add a row identifier
),
"Row",[Row],                                    // Select only our Row and 1 columns
"1",[1]
)
VAR __row12 =
SELECTCOLUMNS(
GROUPBY(
__row2,                                 // We first filter our table to a single row
[Index],                                // Group by row
"2",MAXX(CURRENTGROUP(),[Column1])      // Get the MAX of the first Column and put it in a column named "2"
),
"Row","1"                                   // Add a row identifier
),
"Row",[Row],                                    // Select only our Row and 2 columns
"2",[2]
)
VAR __row13 =
SELECTCOLUMNS(
GROUPBY(
__row3,                                 // We first filter our table to a single row
[Index],                                // Group by row
"3",MAXX(CURRENTGROUP(),[Column1])      // Get the MAX of the first Column and put it in a column named "3"
),
"Row","1"                                   // Add a row identifier
),
"Row",[Row],                                    // Select only our Row and 3 columns
"3",[3]
)
VAR __row14 =
SELECTCOLUMNS(
GROUPBY(
__row4,                                 // We first filter our table to a single row
[Index],                                // Group by row
"4",MAXX(CURRENTGROUP(),[Column1])      // Get the MAX of the first Column and put it in a column named "4"
),
"Row","1"                                   // Add a row identifier
),
"Row",[Row],                                    // Select only our Row and 4 columns
"4",[4]
)

// Second row
VAR __row21 =
SELECTCOLUMNS(
GROUPBY(
__row1,
[Index],
"1",MAXX(CURRENTGROUP(),[Column2])      // Column 1 of second row, get Column2
),
"Row","2"                                   // This is the 2nd row of the transposed table
),
"Row",[Row],                                    // Select only our Row and 1 columns
"1",[1]
)
VAR __row22 =
SELECTCOLUMNS(
GROUPBY(
__row2,
[Index],
"2",MAXX(CURRENTGROUP(),[Column2])      // Column 2 of second row
),
"Row","2"                                   // Row 2
),
"Row",[Row],                                    // Select only our Row and 2 columns
"2",[2]
)
VAR __row23 =
SELECTCOLUMNS(
GROUPBY(
__row3,
[Index],
"3",MAXX(CURRENTGROUP(),[Column2])      // Column 3 of second row
),
"Row","2"                                   // Row 2
),
"Row",[Row],                                    // Select only our Row and 2 columns
"3",[3]
)
VAR __row24 =
SELECTCOLUMNS(
GROUPBY(
__row4,
[Index],
"4",MAXX(CURRENTGROUP(),[Column2])      // Column 4 of second row
),
"Row","2"                                   // Row 2
),
"Row",[Row],                                    // Select only our Row and 4 columns
"4",[4]
)

// Third row
VAR __row31 =
SELECTCOLUMNS(
GROUPBY(
__row1,
[Index],
"1",MAXX(CURRENTGROUP(),[Column3])      // Column3
),
"Row","3"                                   // Row 3
),
"Row",[Row],
"1",[1]
)
VAR __row32 =
SELECTCOLUMNS(
GROUPBY(
__row2,
[Index],
"2",MAXX(CURRENTGROUP(),[Column3])      // Second column
),
"Row","3"
),
"Row",[Row],
"2",[2]
)
VAR __row33 =
SELECTCOLUMNS(
GROUPBY(
__row3,
[Index],
"3",MAXX(CURRENTGROUP(),[Column3])      // Third column
),
"Row","3"
),
"Row",[Row],
"3",[3]
)
VAR __row34 =
SELECTCOLUMNS(
GROUPBY(
__row4,
[Index],
"4",MAXX(CURRENTGROUP(),[Column3])      // Fourth column
),
"Row","3"
),
"Row",[Row],
"4",[4]
)

// Fourth row
VAR __row41 =
SELECTCOLUMNS(
GROUPBY(
__row1,
[Index],
"1",MAXX(CURRENTGROUP(),[Column4])      // Column4
),
"Row","4"                                   // Row 4
),
"Row",[Row],
"1",[1]
)
VAR __row42 =
SELECTCOLUMNS(
GROUPBY(
__row2,
[Index],
"2",MAXX(CURRENTGROUP(),[Column4])      // Second column
),
"Row","4"                                   // Row 4
),
"Row",[Row],
"2",[2]
)
VAR __row43 =
SELECTCOLUMNS(
GROUPBY(
__row3,
[Index],
"3",MAXX(CURRENTGROUP(),[Column4])      // Third column
),
"Row","4"                                   // Row 4
),
"Row",[Row],
"3",[3]
)
VAR __row44 =
SELECTCOLUMNS(
GROUPBY(
__row4,
[Index],
"4",MAXX(CURRENTGROUP(),[Column4])      // Fourth column
),
"Row","4"                                   // Row 4
),
"Row",[Row],
"4",[4]
)

// Join the rows together// Row 1
VAR __transposedRow1a = NATURALINNERJOIN(__row11,__row12)
VAR __transposedRow1b = NATURALINNERJOIN(__row13,__row14)
VAR __transposedRow1 = NATURALINNERJOIN(__transposedRow1a,__transposedRow1b)
// Row 2
VAR __transposedRow2a = NATURALINNERJOIN(__row21,__row22)
VAR __transposedRow2b = NATURALINNERJOIN(__row23,__row24)
VAR __transposedRow2 = NATURALINNERJOIN(__transposedRow2a,__transposedRow2b)
// Row 3
VAR __transposedRow3a = NATURALINNERJOIN(__row31,__row32)
VAR __transposedRow3b = NATURALINNERJOIN(__row33,__row34)
VAR __transposedRow3 = NATURALINNERJOIN(__transposedRow3a,__transposedRow3b)
// Row 4
VAR __transposedRow4a = NATURALINNERJOIN(__row41,__row42)
VAR __transposedRow4b = NATURALINNERJOIN(__row43,__row44)
VAR __transposedRow4 = NATURALINNERJOIN(__transposedRow4a,__transposedRow4b)
// Union these to generate a transposed table
VAR __transposedTable = UNION(__transposedRow1,__transposedRow2,__transposedRow3,__transposedRow4)

RETURN
__transposedTable```