Matrix Multiplication

[KEIRA]

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

[AlexisOlson]

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.

[lbendlin]

@AlexisOlson here are my current WIP versions

[AlexisOlson · Accepted solution]

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.

[KEIRA]

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.

[AlexisOlson]

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] ) )
)

[lbendlin]

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.

[AlexisOlson]

@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] ) )
)

[lbendlin]

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.

[lbendlin]

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]
)

[KEIRA]

Here is another solution:

[lbendlin]

Matrix multiplication is only deterministic if the  number of columns in the first matrix matches the number of rows in the second matrix. By using a left join you are bending the rules.

[AlexisOlson]

@lbendlin Thanks. I'll take a look.

You may also be interested in the comment I just left on @Greg_Deckler's gallery post here:
https://community.powerbi.com/t5/Quick-Measures-Gallery/MMULT/m-p/630231

[KEIRA]

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.

[lbendlin]

"scalar"  in the Power BI sense means "single value, not a list, not a record, not a table"

[lbendlin]

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)

[KEIRA]

Hello, thanks for the comments.
I know what it means to multiply matrices - there is an example in the test file.
I want to multiply the given two matrices by each other (picture two) and I want to get the matrix again.

[lbendlin]

Can you please show the expected result for three currencies.

Your [Covariance] and [Cross Weights] measures are complex, and they need to be called 529 times for each loop of the matrix multiplication.  You  may run into memory issues on this one.

[KEIRA]

[lbendlin]

I don't get that result in Excel

Maybe some rounding issues.

Anyway, what will need to happen is that each currency gets assigned a numeric index (starting from 1). Then you can write a measure that can compute the result of the matrix multiplication for each cell of the crossjoins - all 529 of them. That measure formula will be massive, and I am not sure yet it can be done dynamically. Will have to think about it some more.

[lbendlin]

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.