Forum Discussion
Matrix Multiplication
I think I've got it and it's simpler than I initially anticipated.
Matrix Product = VAR _C1 = SELECTEDVALUE ( H1_CurrencyList[C1] ) VAR _C2 = SELECTEDVALUE ( H2_CurrencyList[C2] ) VAR _Matrix_ = ADDCOLUMNS ( ALL ( H1_CurrencyList[C1] ), "Row_1", VAR _C = H1_CurrencyList[C1] RETURN CALCULATE ( [Covariance], H1_CurrencyList[C1] = _C1, H2_CurrencyList[C2] = _C ), "Col_2", VAR _C = H1_CurrencyList[C1] RETURN CALCULATE ( [Cross Weights], H1_CurrencyList[C1] = _C, H2_CurrencyList[C2] = _C2 ) ) RETURN SUMX ( _Matrix_, [Row_1] * [Col_2] )Having a square matrix that's a cross product of a list of currencies with itself gives a nice solution that only requires one evaluation each of [Covariance] and [Cross Weights] per currency per cell in the result matrix (each of the N^2 result cells requires 2N measure calls for N currencies).
This is simpler than my comment on the gallery post I mentioned previously since I don't need to load the entire matrices, just the relevant row & column from each one. The main difficulty there is just setting up the indexing and filtering for the parts needed. The key logic in both is the same sum product.
I found a solution that's even shorter by taking advantage of the evaluation context.
It's easiest if I have an independent currency dimension Dim_Currency[CUR]:
Matrix Product =
SUMX (
VALUES ( Dim_Currency[CUR] ),
CALCULATE ( [Covariance], TREATAS ( { Dim_Currency[CUR] }, H2_CurrencyList[C2] ) ) *
CALCULATE ( [Cross Weights], TREATAS ( { Dim_Currency[CUR] }, H1_CurrencyList[C1] ) )
)
Without the independent column (i.e. if I used ALL ( H1_CurrencyList[C1]) for the first argument), the data lineage of the column being iterated over overwrites the evaluation context during the context transition induced by CALCULATION (which is why I had to specify _C1 and _C2 in filter arguments for my previous solution).
After reviewing the SQLBI data lineage article, I realized I can skip the need for a new independent table by breaking the data lineage with an empty string concatenation. Thus the previous DAX can be replaced with this:
Matrix Product =
SUMX (
SELECTCOLUMNS ( ALL ( H1_CurrencyList[C1] ), "CUR", H1_CurrencyList[C1] & "" ),
CALCULATE ( [Covariance], TREATAS ( { [CUR] }, H2_CurrencyList[C2] ) ) *
CALCULATE ( [Cross Weights], TREATAS ( { [CUR] }, H1_CurrencyList[C1] ) )
)
Not sure if this resulting in the correct output. Neither ALL nor VALUES is guaranteeing a sort order, and you may risk multiplying the wrong elements. (also keep in mind that matrix multiplication is not commutative) Here is an variation of a measure that is horribly inefficient due to the cross join (i couldn't get the naturalinnerjoin to work) but it does produce the correct output.
Matrix Product Measure =
SUMX (
FILTER (
CROSSJOIN ( GROUPBY ( MatrixA, [ca], [va] ), GROUPBY ( Matrixb, [rb], [vb] ) ),
[ca] = [rb]
),
[va] * [vb]
)
The visual above it uses your version and it comes out a bit too high.
lbendlin You didn't quite implement it correctly.
This is what you had:
Matrix Product = SUMX ( SELECTCOLUMNS ( ALL ( MatrixA[ra] ), "Col", MatrixA[ra] & "" ), CALCULATE ( sum(MatrixA[va]), TREATAS ( { [Col] }, MatrixB[cb] ) ) * CALCULATE ( sum(MatrixB[vb]), TREATAS ( { [Col] }, MatrixA[ra] ) ) )This is a corrected version:
Matrix Product = SUMX ( SELECTCOLUMNS ( ALL ( MatrixA[ra] ), "Col", MatrixA[ra] & "" ), CALCULATE ( SUM ( MatrixA[va] ), TREATAS ( { [Col] }, MatrixA[ca] ) ) * CALCULATE ( SUM ( MatrixB[vb] ), TREATAS ( { [Col] }, MatrixB[rb] ) ) )yes, that fixed it. Now the question is which of the many implementations is the most efficient. 🙂
For example changing your ALL to ALLSELECTED seems to improve performance too.
Ok, with your lineage breaker trick I got the NaturalInnerJoin to work
Mv2 = SUMX ( NATURALINNERJOIN(SELECTCOLUMNS(MatrixA,"c",[ca]+0,"va",[va]), SELECTCOLUMNS ( MatrixB, "c",[rb]+0, "vb",[vb] ) ), [va] * [vb] )
