Perform an operation combining two columns from different tables

[Otege]

Hello,

I have developed few reports on Power BI without problems while I didn't need to use DAX, now that i think that I have to, and I'm so lost. I would like the expose my case to see if someone can guide me.

Let me explain, I have that tables:

As you can see there's a relationship M:N between Transaccions (which contains payments received to Workers and the parameters related) and Cluster (where the user can create Clusters where it specifies parameters sets and the sign of each).

The idea is that on Power Bi a user can select a cluster and it will show from transaccions the value of the sum of each parameter specified on the cluster taking into account their sign (+ o -). Summarizing an operation like sum(Value*Sign).

I know I could do on power query a join between that two tables getting the combination, but I'm getting a really huge table and the simple creation of new Clusters (with others combinations) increases too much the table, so I though DAX would be the solution.

I was thinking on something like performing on DAX a naturalinnerjoin and then use some function to multiply Value with Signal and then sum but It would be similar than the solution on power query no?

                 SUMX(NATURALINNERJOIN(Transaccions,'Cluster'), Transaccions[Value]*'Cluster'[Sign])

In any case I couldn't get the solution, it tells me that it coudn't find related combination colums.

I also tried some options using the related funcion but always says that can find the column sign on the table Cluster (like there was some problem with relationships).

I would be very grateful if you could help me,

Regards.

[tamerj1 · Accepted solution]

Hi @Otege 
Please try one of the following 

Measure1 =
SUMX (
    Transaccions,
    SUMX ( RELATEDTABLE ( Cluster ), Transaccions[Value] * Cluster[Sign] )
)

Measure1 =
SUMX (
    Transaccions,
    Transaccions[Value] * PRODUCTX ( RELATEDTABLE ( Cluster ), Cluster[Sign] )
)

Measure1 =
SUMX ( Transaccions, SUMX ( Cluster, Transaccions[Value] * Cluster[Sign] ) )

Measure1 =
SUMX ( Transaccions, Transaccions[Value] * PRODUCTX ( Cluster, Cluster[Sign] ) )

[Otege]

Hello tamerj1,

First of all, thanks for all thats examples, they helped me a lot to understand differences between using that funcions in one order or another. The first measure you said it's which one I was looking for, but I'm not able to understand the differences between these two:

Measure1 =
SUMX (
    Transaccions,
    SUMX ( RELATEDTABLE ( Cluster ), Transaccions[Value] * Cluster[Sign] )
)

Measure1 =
SUMX ( Transaccions, SUMX ( Cluster, Transaccions[Value] * Cluster[Sign] ) )

 I mean, in the data model there's a relationship between that two tables, but to adquire the results I need it's necessary to use the relatedtable when they are also related, how it works that function in that context?

Thanks 🙂

[tamerj1 · Accepted solution]

@Otege 
SUMX creates a row context where the other table is filtered only by the current filter context (which contains much more rows than related to each row of the first table). Therefore, you need RELATEDTABLE or CALCULATETABLE to filter it down to only the relevant rows.

[Otege]

Thanks again for all your fast answer, I marked your answer as solution. 

Regards

[tamerj1 · Accepted solution]

Hi @Otege 
Please try one of the following 

Measure1 =
SUMX (
    Transaccions,
    SUMX ( RELATEDTABLE ( Cluster ), Transaccions[Value] * Cluster[Sign] )
)

Measure1 =
SUMX (
    Transaccions,
    Transaccions[Value] * PRODUCTX ( RELATEDTABLE ( Cluster ), Cluster[Sign] )
)

Measure1 =
SUMX ( Transaccions, SUMX ( Cluster, Transaccions[Value] * Cluster[Sign] ) )

Measure1 =
SUMX ( Transaccions, Transaccions[Value] * PRODUCTX ( Cluster, Cluster[Sign] ) )