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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
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
Employee
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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