How to create a measure using data of 2 seperate tables

[Anonymous]

Hey guys,

I've got 2 tables.
Table 1: Showing all customers that opened a specific e-mail campaign
Table 2: Showing all clicks per url of an e-mail campaign.

Example Table 1:

Example Table 2:

I made one measure:
Clicks = SUM('Data'[count(*)])
With this I can get the amount of clicks per url.
What I want to do is divide this with the total amount of opens of the mailing_id.

But in my measure I can't seem to add a different measure from another table.
Anyway to solve this?

With kind regards

[amitchandak · Accepted solution]

@Anonymous , Create a common table mailing id and join with both table, then you analyze measure by common table

mailing id = distinct(union(distinct(Table1[mailing id]),distinct(Table2[mailing id])))

https://www.seerinteractive.com/blog/join-many-many-power-bi/

[Anonymous]

Hey Amitchandak,

I understand your solution. However before I can join the two tables I need to aggregate the opens and clicks. As both tables have a granularity on e-mail level. Every row is an individual recipient. In my example above I left out the e-mail detail due to privacy reasons.

So I need to figure out how to create an aggregated table first, without losing the original table in my model. 

Example:

Recipient, URL, Clicks
1, 1, 1
1, 2, 1
1, 3, 1
2, 2, 1
2, 3, 1
3, 1, 1
3, 3, 1

How to translate this column into this:
URL, Clicks
1, 2
2, 2
3, 3

With kind regards,

[Anonymous]

Aah, I found out the GROUPBY function in combination with COUNTX and CURRENTGROUP.
Anyway, thanks for putting me on the right track AmitChandak!