Greg_Deckler
7 years agoCommunity Champion
MMULT
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 dyna...
AlexisOlson
4 years agoSuper User
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