calculations within non-related tables

[SarahAlsterspre]

hey,

I have to tables from two different sources and one column contains a text which contains an ID that is defined in another table. 

(I cannot publish a Message with tables - i am getting errors)

Source 1:

Company Name          Sponsor (SP-ID)            NL-ID

kuwerhgidufg              SP0001                        NL-0001

keurghk                       SP0002                        NL-0008

...                                   ....                                .....

Source 2 (Newsletter):

Title                       Openers                 URL

ifuwarhefkl             267                     htt...text...NL-0001... .

luadrhoi                  23829                 htt.....NL-0008...

iufhsdfkön             12882                  htt... NL-0001

...                             ...                           ...

-> I would like to have a third table or measurement that results in:

Sponsors             Total Openers

SP0001                13.149

SP0002                 .....

The problem is that these to tables so far cannot be related in any way.

In excel I would build something like "if Column [URL] contains [NL-ID] then sum up column [Openers]"

I am looking forward to solutions! 

[amitchandak · Accepted solution]

@SarahAlsterspre , You can have column like this in a table 1

sumx(filter(Newsletter, search( Source1[NL-ID], Newsletter[URL],,0)>0),Source1[Openers])

refer: https://www.youtube.com/watch?v=czNHt7UXIe8

[westwrightj]

Hey @SarahAlsterspre ,

    Okay, so I think I see what you are going for here and there are a few different ways you can go about it. The solution I've provided is not the most eqloquent or complex but should meet the outcome you listed.

I've used the following sample data for this solution

So we can actually make a measure like the following to get the outcome table you were looking for without connecting anything. In this case this measure would only work properly in a table format and in this example is calculating the average opener number

TheMeasurement = 

var TheNL_ID = MAX('Source 1'[NL-ID])

return

CALCULATE(AVERAGE('Source 2'[Openers]), CONTAINSSTRING('Source 2'[URL], TheNL_ID))

Let me know if that solution works or if we want to go back and tinker with it some more

[camargos88]

@SarahAlsterspre ,

You can use this measure:

_Openers = 
VAR _ndID = SELECTEDVALUE('Table'[NL-ID])
RETURN CALCULATE(SUM('Table (2)'[Openers]), CONTAINSSTRING('Table (2)'[URL], _ndID))

If you have a patterns, you can split the url column and related both tables, avoid any problem.