cancel
Showing results for 
Search instead for 
Did you mean: 

TRANSPOSE

Super User
439 Views
Super User
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(
        ADDCOLUMNS(
            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(
        ADDCOLUMNS(
            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(
        ADDCOLUMNS(
            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(
        ADDCOLUMNS(
            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(
        ADDCOLUMNS(
            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(
        ADDCOLUMNS(
            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(
        ADDCOLUMNS(
            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(
        ADDCOLUMNS(
            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(
        ADDCOLUMNS(
            GROUPBY(
                __row1,
                [Index],
                "1",MAXX(CURRENTGROUP(),[Column3])      // Column3
            ),
            "Row","3"                                   // Row 3
        ),
        "Row",[Row],
        "1",[1]
    )
VAR __row32 = 
    SELECTCOLUMNS(
        ADDCOLUMNS(
            GROUPBY(
                __row2,                                 
                [Index],                                
                "2",MAXX(CURRENTGROUP(),[Column3])      // Second column
            ),
            "Row","3"                                   
        ),
        "Row",[Row],                                    
        "2",[2]
    )
VAR __row33 = 
    SELECTCOLUMNS(
        ADDCOLUMNS(
            GROUPBY(
                __row3,                                 
                [Index],                                
                "3",MAXX(CURRENTGROUP(),[Column3])      // Third column
            ),
            "Row","3"                                   
        ),
        "Row",[Row],                                    
        "3",[3]
    )
VAR __row34 = 
    SELECTCOLUMNS(
        ADDCOLUMNS(
            GROUPBY(
                __row4,                                 
                [Index],                                
                "4",MAXX(CURRENTGROUP(),[Column3])      // Fourth column
            ),
            "Row","3"                                   
        ),
        "Row",[Row],                                    
        "4",[4]
    )

// Fourth row
VAR __row41 = 
    SELECTCOLUMNS(
        ADDCOLUMNS(
            GROUPBY(
                __row1,
                [Index],
                "1",MAXX(CURRENTGROUP(),[Column4])      // Column4
            ),
            "Row","4"                                   // Row 4
        ),
        "Row",[Row],
        "1",[1]
    )
VAR __row42 = 
    SELECTCOLUMNS(
        ADDCOLUMNS(
            GROUPBY(
                __row2,                                 
                [Index],                                
                "2",MAXX(CURRENTGROUP(),[Column4])      // Second column
            ),
            "Row","4"                                   // Row 4
        ),
        "Row",[Row],                                    
        "2",[2]
    )
VAR __row43 = 
    SELECTCOLUMNS(
        ADDCOLUMNS(
            GROUPBY(
                __row3,                                 
                [Index],                                
                "3",MAXX(CURRENTGROUP(),[Column4])      // Third column
            ),
            "Row","4"                                   // Row 4
        ),
        "Row",[Row],                                    
        "3",[3]
    )
VAR __row44 = 
    SELECTCOLUMNS(
        ADDCOLUMNS(
            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

 

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!