Relationship between tables with aggregation (Table Chart)

[raphaelcamposs]

Hello Everyone.

I have 2 tables in PowerBI.  

Table 1 (Keyword, Country, Webiste, ClickDate, NumClicks)
Table 2 (Keyword, Country, Website, TotalDeposit, NumDeposit)

I need to create a table chart with information from Table 2 + Column NumClicks from Table 1. 

The issue happens when I have to combine those information, the chart displays the TotalNumClicks for each row in Table 2. Please, find the image attached. 

1) I tried to create a relationship between these tables but the PowerBI did not allow because one of the columns must have unique values.

2) I tried to create three new tables (TabelaKeyword, TabelaCountry, TabelaWebsite) and relate each one to the main tables also it did not work. 

3) I tried to Merge these tables but no joy. 

The chart continues to display the Total NumClicks as you can see on the image that I attached. 

 
Does anyone have an idea to sort it out, please?

Best,

Raphael

[v-yulgu-msft · Accepted solution]

Hi @raphaelcamposs,

You could create below measure:

Total NumClicks =
CALCULATE (
    SUM ( Table1[NumClicks] ),
    FILTER (
        Table1,
        Table1[Keyword] = LASTNONBLANK ( Table2[Keyword], 1 )
            && Table1[Country] = LASTNONBLANK ( Table2[Country], 1 )
            && Table1[Webiste] = LASTNONBLANK ( Table2[Webiste], 1 )
    )
)

Then, create a table visual with information from Table 2 and above measure.

Best regards,

Yuliana Gu

[YoungJim88]

Hi @v-yulgu-msft ,

Excellent reply. This is exactly what I was looking for.

Best regards,

James.

[raphaelcamposs]

Fantastic! It worked! Thank you so much!

[Anonymous]

So from the sounds of it you need a many to many relationship, which is only possible with the preview feature which cant be published to the service.

You can still use the old workaround where you create a table with the unique nonblank values for the column you with to join both tales with then create bdirectional  one to many relationships between these tables and you should be able to do some crossfilering.

If this doesnt work I would suggest trying to join the tables in the query editor, or in your data source if you are getting the data from a SQL server or similar.