cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

## Relationship between tables with aggregation (Table Chart)

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

1 ACCEPTED SOLUTION
Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
New Member

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

Best regards,

James.

Regular Visitor

Fantastic! It worked! Thank you so much!

Anonymous
Not applicable

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.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors