Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
raphaelcamposs
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,

PowerBI.jpgRaphael

1 ACCEPTED SOLUTION
v-yulgu-msft
Microsoft Employee
Microsoft Employee

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

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.

View solution in original post

4 REPLIES 4
v-yulgu-msft
Microsoft Employee
Microsoft Employee

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

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.

Hi @v-yulgu-msft ,

 

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

 

Best regards,

 

James.

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.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.