Forum Discussion

raphaelcamposs's avatar
raphaelcamposs
Regular Visitor
8 years ago
Solved

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 t...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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.