Matrix Multiplication using DAX

A few weeks ago I read a question on matrix multiplication with DAX. The user asked if it was possible to implement matrix multiplication using DAX and how.

The answer is that the DAX language doesn’t have the matrix construct, and therefore the best solution is to move the matrix multiplication at the source level using a more suitable language. Even if an implementation was possible, it would be a complex and awkward code, that would be inefficient and hard to read.

The purpose of this article is to show some inefficient and hard-to-read code that does matrix multiplication. It’s meant for DAX and mathematics geeks to have some fun.

The first problem to solve is how to represent matrix with DAX. Using a table directly as a matrix would not be possible, since DAX tables cannot be indexed by row and column indexes

The solution I found is to implement a table with a row per each cell, with row, column and value columns

This table representation of a matrix can be shown in a report as a matrix using the Matrix visual, with the column row on the Rows, the column on the Columns, and the value on the Values, which is pretty straightforward.

Having row and column indexes, it becomes possible to implement matrix multiplication. To access the single value of a cell, I used the LOOKUPVALUE DAX function, that in my opinion is the more readable function for that purpose.

A x B = GENERATE( 
    ALL( A[row] ), 
    ADDCOLUMNS(
        ALL( B[column] ),
        "value",
        SUMX( 
            ALL( A[column] ),
            LOOKUPVALUE( 
                A[value], 
                A[row], A[row],
                A[column], A[column]
            )
            *
            LOOKUPVALUE( 
                B[value], 
                B[row], A[column],
                B[column], B[column]
            )
        )
    )    
)

This code returns a table. The resulting table will use the same matrix representation using the row, column and value columns.

The multiplication of a matrix with l rows and m columns times a matrix with m rows and n columns will return a matrix with l rows and n columns. The details on how matrix multiplication works can be found on wikipedia Matrix multiplication page .

The code multiplies matrix A with matrix B. Matrixes can be of any size, but to keep code simple there is no test on the condition that the number of columns of A are equal to the number of columns of B.

The GENERATE iterates on the rows of matrix A, and then, per each row it executes the ADDCOLUMNS to iterate on the columns of table B.

Per each pair of values of A[row] and B[colum], the SUMX is executed to iterate on A[column].

In this explanation we will indicate A[row] as r, B[column] as c and A[column] as i, so we can indicate a matrix cell as Matrix(row, column), like for instance A(r, i).

Per each iteration the LOOKUPVALUES are used to retrieve the A(c, i) and B(i, c) elements to be multiplied and then added up by the SUMX.

In conclusion, this code is not so hard-to-read, but it assumes that we have a suitable representation for the matrixes and the result is a table.

More code would be unavoidable to be able to first prepare the matrix representation and implementing the multiplication using a measure instead of a calculated table could be harder. But I still didn’t try it, maybe in the future.

For my sample I implemented two small matrixes using the DATATABLE function

A = DATATABLE(
        "row", INTEGER,
        "column", INTEGER,
        "value", DOUBLE, 
        {
            { 1, 1, 1.0 },
            { 1, 2, 2.0 },
            { 2, 1, 3.0 },
            { 2, 2, 4.0 },
            { 3, 1, 5.0 },
            { 3, 2, 6.0 }
        }
    )

B = DATATABLE(
        "row", INTEGER,
        "column", INTEGER,
        "value", DOUBLE, 
        {
            { 1, 1, 11.0 },
            { 1, 2, 12.0 },
            { 2, 1, 13.0 },
            { 2, 2, 14.0 }
        }
    )

Then I built a report to show the matrix multiplication result

The sample file for this article can be downloaded from my github shared project.