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 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.
Yes, I saw your StackOverflow solution and I love your answer.
Wow amazing, A very good way to solve the case.
Congratulations!
Everyone where I asked this question told me that it is almost "impossible" to do this (I mean, I'd have to write long code and stuff...). I found a way to solve this problem and left this post - waiting for someone who likes to do "impossible" - glad to see you here too.
Thank you very much for your solution and help.
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] ) ) )
