Forum Discussion

KEIRA's avatar
KEIRA
Frequent Visitor
4 years ago
Solved

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:               ...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    3 years ago

    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.

    MatrixProduct.pbix22 KB
AlexisOlson's avatar
AlexisOlson
Super User
3 years ago

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's avatar
lbendlin
Super User
3 years ago

AlexisOlson here are my current WIP versions

Test Example.pbix44 KB
MyData.pbix1.8 MB
  • AlexisOlson's avatar
    AlexisOlson
    Super User
    3 years ago

    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.

    MatrixProduct.pbix22 KB
    • KEIRA's avatar
      KEIRA
      Frequent Visitor
      3 years ago

      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's avatar
        AlexisOlson
        Super User
        3 years ago

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