Create measure with filter from tables without relation

[RemiAnthonise]

Hi guys,

I'm struggling with a problem and I can't find the right way to fix it. It must be easy to solve this but I can't create my second filter.

I have two tables without a relationship. 1 table gives me employees with targets and revenue (table called Overview). My other table gives me amounts per deal stage per owner (table called Deals). In my original file I have more stages, in this sample file only 3, so in my original file I filtered on the stages I need. This works.

The 'to do' I need to calculate is as follows: 
Target revenue - Real revenue - SUM Deals Amounts where Deal stage =

- Contract verzonden

- Koopintentie aanwezig

- Offerte verstuurd

Measure To do = CALCULATE(SUM(Overview[Target revenue])) - CALCULATE(SUM(Overview[Real revenue])) - CALCULATE(SUM('Deals'[Amount]); 'Deals'[Deal Stage] = "Contract verzonden" || 'Deals'[Deal Stage] = "Koopintentie aanwezig" || 'Deals'[Deal Stage] = "Offerte verstuurd")

This measure works. Now the measure also needs to filter on the right employees. In fact, both tables are fact tables so I can't create an relationship because of n:n relationship. The field 'Name' in table Overview (no distinct values) has the same data als the field 'Deal owner' in the table Deals (also no distinct values). I somehow have to create a Name-filter in my measure so that the calculated Funnel (I've created a new measure called Measure Funnel to show it easily) filters only his 'own' name.

See sample file here.

Thanks a lot!

[v-lili6-msft · Accepted solution]

hi, @RemiAnthonise

After my test on your pbix, You need to create a relationship between Overview with Overview.

For you don't want to create a n:n relationship, you could add a name table, then create the relationship between them like this:

Result:

Best Regards,

Lin

[RemiAnthonise]

Sorry for my late reply @v-lili6-msft (holidays 🙂 ) but that solved it.

Cheers.