Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Excel has an MMULT function for multiplying 2 tables/matrices. DAX does not. So I created this. Leverages my previous Quick Measure TRANSPOSE. So, if you were wondering why you would ever need a dynamic TRANSPOSE function in DAX, here is an example, suppose you wanted to build a dynamic matrix multiplication calculator in Power BI...
This is the code for the dynamic matrix multiplication solution. At over 300 lines of DAX code, it is probably some of the most complex DAX code I have written yet. Also included is a table versions.
MMULT = // As a dynamic measure, we just first build our dynamic tables VAR __table1StartRow1 = SELECTCOLUMNS( ADDCOLUMNS( 'Solution', "Index",1, "Column1",[T3R1C1 Value], "Column2",[T3R1C2 Value], "Column3",[T3R1C3 Value], "Column4",[T3R1C4 Value] ), "Index",[Index], "Column1",[Column1], "Column2",[Column2], "Column3",[Column3], "Column4",[Column4] ) VAR __table1StartRow2 = SELECTCOLUMNS( ADDCOLUMNS( 'Solution', "Index",2, "Column1",[T3R2C1 Value], "Column2",[T3R2C2 Value], "Column3",[T3R2C3 Value], "Column4",[T3R2C4 Value] ), "Index",[Index], "Column1",[Column1], "Column2",[Column2], "Column3",[Column3], "Column4",[Column4] ) VAR __table1StartRow3 = SELECTCOLUMNS( ADDCOLUMNS( 'Solution', "Index",3, "Column1",[T3R3C1 Value], "Column2",[T3R3C2 Value], "Column3",[T3R3C3 Value], "Column4",[T3R3C4 Value] ), "Index",[Index], "Column1",[Column1], "Column2",[Column2], "Column3",[Column3], "Column4",[Column4] ) VAR __table2StartRow1 = SELECTCOLUMNS( ADDCOLUMNS( 'Solution', "Index",1, "Column1",[T4R1C1 Value], "Column2",[T4R1C2 Value] ), "Index",[Index], "Column1",[Column1], "Column2",[Column2] ) VAR __table2StartRow2 = SELECTCOLUMNS( ADDCOLUMNS( 'Solution', "Index",2, "Column1",[T4R2C1 Value], "Column2",[T4R2C2 Value] ), "Index",[Index], "Column1",[Column1], "Column2",[Column2] ) VAR __table2StartRow3 = SELECTCOLUMNS( ADDCOLUMNS( 'Solution', "Index",3, "Column1",[T4R3C1 Value], "Column2",[T4R3C2 Value] ), "Index",[Index], "Column1",[Column1], "Column2",[Column2] ) VAR __table2StartRow4 = SELECTCOLUMNS( ADDCOLUMNS( 'Solution', "Index",4, "Column1",[T4R4C1 Value], "Column2",[T4R4C2 Value] ), "Index",[Index], "Column1",[Column1], "Column2",[Column2] ) VAR __table1Start = UNION(__table1StartRow1,__table1StartRow2,__table1StartRow3) VAR __table2Start = UNION(__table2StartRow1,__table2StartRow2,__table2StartRow3,__table2StartRow4) // 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. // // NOTE! // Remember, you can only multiply matrices together in the form of R1xC1, R2,C2 where C1=R2. // In other words, there must be the same number of columns in the first matrix as there are rows in the second. VAR __row11 = SELECTCOLUMNS( ADDCOLUMNS( GROUPBY( FILTER(__table2Start,[Index]=1), // 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( FILTER(__table2Start,[Index]=2), [Index], "2",MAXX(CURRENTGROUP(),[Column1]) // As above but this will be in a column named "2" ), "Row","1" // Same row identifier as above ), "Row",[Row], "2",[2] ) VAR __row13 = SELECTCOLUMNS( ADDCOLUMNS( GROUPBY( FILTER(__table2Start,[Index]=3), [Index], "3",MAXX(CURRENTGROUP(),[Column1]) ), "Row","1" ), "Row",[Row], "3",[3] ) VAR __row14 = SELECTCOLUMNS( ADDCOLUMNS( GROUPBY( FILTER(__table2Start,[Index]=4), [Index], "4",MAXX(CURRENTGROUP(),[Column1]) ), "Row","1" ), "Row",[Row], "4",[4] ) // This completes the values for our first row // We need a second row since we have 2 columns in the original table, same process as above only using our second column // and a row identifier of 2 VAR __row21 = SELECTCOLUMNS( ADDCOLUMNS( GROUPBY( FILTER(__table2Start,[Index]=1), [Index], "1",MAXX(CURRENTGROUP(),[Column2]) ), "Row","2" ), "Row",[Row], "1",[1] ) VAR __row22 = SELECTCOLUMNS( ADDCOLUMNS( GROUPBY( FILTER(__table2Start,[Index]=2), [Index], "2",MAXX(CURRENTGROUP(),[Column2]) ), "Row","2" ), "Row",[Row], "2",[2] ) VAR __row23 = SELECTCOLUMNS( ADDCOLUMNS( GROUPBY( FILTER(__table2Start,[Index]=3), [Index], "3",MAXX(CURRENTGROUP(),[Column2]) ), "Row","2" ), "Row",[Row], "3",[3] ) VAR __row24 = SELECTCOLUMNS( ADDCOLUMNS( GROUPBY( FILTER(__table2Start,[Index]=4), [Index], "4",MAXX(CURRENTGROUP(),[Column2]) ), "Row","2" ), "Row",[Row], "4",[4] ) // Join our first set of individual row values into a single row, Row 1 VAR __table1a = NATURALINNERJOIN(__row11,__row12) VAR __table1b = NATURALINNERJOIN(__row13,__row14) VAR __table1Final = NATURALINNERJOIN(__table1a,__table1b) // Join our second set of individual row values into a single row, Row 2 VAR __table2a = NATURALINNERJOIN(__row21,__row22) VAR __table2b = NATURALINNERJOIN(__row23,__row24) VAR __table2Final = NATURALINNERJOIN(__table2a,__table2b) // Return the UNION of our two final tables as the transposed table VAR __transposedTable = UNION(__table1Final,__table2Final) // Now that we have our original first table and our new transposed second table, we can combine them // We select our Index column from our first table and call it "Row" as well as the other columns VAR __table3 = SELECTCOLUMNS(__table1Start,"Row",[Index],"Column1",[Column1],"Column2",[Column2],"Column3",[Column3],"Column4",[Column4]) // We select our Row column from our transposed table and call it Column, as well as the other columns VAR __table4 = SELECTCOLUMNS(__transposedTable,"Column",[Row],"1",[1],"2",[2],"3",[3],"4",[4]) // We create a cartesian product of the two tables and place it into a new table // We add a column to the result of this that performs our multiplication VAR __table5 = ADDCOLUMNS(GENERATE(__table3,__table4),"Value",[Column1]*[1] + [Column2]*[2] + [Column3]*[3] + [Column4]*[4]) // We now need to transpose the table back in order to achieve our final table. // A 3x4 matrix multipled with a 4x2 matrix will result in a 3x2 matrix // This is similar to above except that the values we want are in a single column, Value. // However, this is not a problem since we have both Row and Column identifiers VAR __row11a = GROUPBY( FILTER(__table5,[Row]=1 && [Column]="1"), [Row], "1",MAXX(CURRENTGROUP(),[Value]) ) VAR __row12a = GROUPBY( FILTER(__table5,[Row]=1 && [Column]="2"), [Row], "2",MAXX(CURRENTGROUP(),[Value]) ) VAR __row21a = GROUPBY( FILTER(__table5,[Row]=2 && [Column]="1"), [Row], "1",MAXX(CURRENTGROUP(),[Value]) ) VAR __row22a = GROUPBY( FILTER(__table5,[Row]=2 && [Column]="2"), [Row], "2",MAXX(CURRENTGROUP(),[Value]) ) VAR __row31a = GROUPBY( FILTER(__table5,[Row]=3 && [Column]="1"), [Row], "1",MAXX(CURRENTGROUP(),[Value]) ) VAR __row32a = GROUPBY( FILTER(__table5,[Row]=3 && [Column]="2"), [Row], "2",MAXX(CURRENTGROUP(),[Value]) ) // Again, create our rows by joining our individual row values together VAR __table1aFinal = NATURALINNERJOIN(__row11a,__row12a) VAR __table2aFinal = NATURALINNERJOIN(__row21a,__row22a) VAR __table3aFinal = NATURALINNERJOIN(__row31a,__row32a) // Use UNION to create our final table // We include our Row column and Column identifiers so that we can specifically reference particular values VAR __tableFinal = UNION(__table1aFinal,__table2aFinal,__table3aFinal) // Now we must figure out where in the solution matrix we are to present the correct number VAR __solutionRow = MAX('Solution'[Row]) VAR __solutionColumn = MAX('Solution'[Column]) RETURN SWITCH(TRUE(), __solutionRow = 1 && __solutionColumn = 1, MAXX(FILTER(__tableFinal,[Row]=1),[1]), __solutionRow = 2 && __solutionColumn = 1, MAXX(FILTER(__tableFinal,[Row]=2),[1]), __solutionRow = 3 && __solutionColumn = 1, MAXX(FILTER(__tableFinal,[Row]=3),[1]), __solutionRow = 1 && __solutionColumn = 2, MAXX(FILTER(__tableFinal,[Row]=1),[2]), __solutionRow = 2 && __solutionColumn = 2, MAXX(FILTER(__tableFinal,[Row]=2),[2]), __solutionRow = 3 && __solutionColumn = 2, MAXX(FILTER(__tableFinal,[Row]=3),[2]) )
eyJrIjoiYmE1ODdjNGUtMTVkOS00NTlhLTliOTQtMTViMjhiMzUwMzA4IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9
This is way more complex than necessary.
After converting the slicers to tables, we only need about an additional 10 lines.
MMULT =
VAR _Row_i = SELECTEDVALUE ( Solution[Row] )
VAR _Col_j = SELECTEDVALUE ( Solution[Column] )
VAR _A_ =
SELECTCOLUMNS (
{
( 1, 1, [T3R1C1 Value] ),
( 1, 2, [T3R1C2 Value] ),
( 1, 3, [T3R1C3 Value] ),
( 1, 4, [T3R1C4 Value] ),
( 2, 1, [T3R2C1 Value] ),
( 2, 2, [T3R2C2 Value] ),
( 2, 3, [T3R2C3 Value] ),
( 2, 4, [T3R2C4 Value] ),
( 3, 1, [T3R3C1 Value] ),
( 3, 2, [T3R3C2 Value] ),
( 3, 3, [T3R3C3 Value] ),
( 3, 4, [T3R3C4 Value] )
},
"Row", [Value1],
"Col", [Value2],
"Val", [Value3]
)
VAR _B_ =
SELECTCOLUMNS (
{
( 1, 1, [T4R1C1 Value] ),
( 1, 2, [T4R1C2 Value] ),
( 2, 1, [T4R2C1 Value] ),
( 2, 2, [T4R2C2 Value] ),
( 3, 1, [T4R3C1 Value] ),
( 3, 2, [T4R3C2 Value] ),
( 4, 1, [T4R4C1 Value] ),
( 4, 2, [T4R4C2 Value] )
},
"Row", [Value1],
"Col", [Value2],
"Val", [Value3]
)
VAR _n = MAXX ( _A_, [Col] )
VAR _Index_ = SELECTCOLUMNS ( GENERATESERIES ( 1, _n ), "Index", [Value] )
VAR _Soluion_ij_ =
ADDCOLUMNS (
_Index_,
"A_Row_i", MAXX ( FILTER ( _A_, [Row] = _Row_i && [Col] = [Index] ), [Val] ),
"B_Col_j", MAXX ( FILTER ( _B_, [Row] = [Index] && [Col] = _Col_j ), [Val] )
)
RETURN
SUMX ( _Soluion_ij_, [A_Row_i] * [B_Col_j] )
I adapted this from one of my 2018 posts on StackOverflow:
https://stackoverflow.com/questions/51882793/multiply-matrices-in-dax