Forum Discussion
Matrix Multiplication
Hello everyone,
I really need your help.
I have two matrices and I want to multiply the matrices with each other.
I found a way to do it, as you can see in the picture:
but the matrix written in the test file only works on physical tables.
The matrices on my real data are virtual tables (My Data File):
and I need your help to find a way to multiply these matrices with each other.
Can anyone help me? 😞
Both files are attached here:
Test File
My Data
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.
20 Replies
What is your next step? What are you planning to do after the matrix multiplication?
Measures can use table variables during the computation process but the final result of a measure must be a scalar value.
Yes, the result of matrix multiplication must be scalar (it cannot be a table as in the test file). When I drop this measure into columns C1 and C2 - I should get a matrix.
"scalar" in the Power BI sense means "single value, not a list, not a record, not a table"
I think we have a terminoloy issue here. What you seem to be trying to do is to apply weighting to one of your tables. "Multiplying matrices" has a very different meaning.
Matrix Multiplication | How to Multiply Matrices | Formula & Examples (byjus.com)
It looks like you found my StackOverflow post.
I'm interested in helping (writing this as a measure instead of a calculated table sound like a fun challenge) but the files you've linked to appear to have been deleted.
AlexisOlson here are my current WIP versions
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.
