Calculate only IF value in the other table.. Summarize?

[ING_BT]

Hello, 

I'm trying to calculate the sum of Costs, only when a campaign has users. 

For that I have these two tables, connected to a join table.

The Join table is a distinct of Campaing IDs from Table One. 

Join to Table 1 => Both Direction

Join to Table 2 => One to Many 

If I select a whole week (monday to sunday) this is the expected results : 
So, In a table without dates, how can I have the sum of costs only when Users > 0

Results
Campaign ID	Users	Costs
1	455	924
2	300	400

Table 1
Date	Users	Campagn ID
Monday	0	1
Tuesday	0	1
Wednesday	200	1
Thursday	250	1
Friday	5	1
Saturday	150	2
Sunday	150	2

Table 2
Date	Cost	Campagn ID
Monday	50	1
Tuesday	65	1
Wednesday	323	1
Thursday	566	1
Friday	35	1
Saturday	200	2
Sunday	200	2

Table 3 (join talbe)

Campagn ID

1

2

Thank you for your help!

Best,

Boris

[ING_BT]

Hello Rohit! 

Is there a way to do it in a measure? 
As my model and tables are way more complexe than this, and it doesn't work with many to many relationship like theses ones.

Thank you in advance!

Best,

BT

[v-easonf-msft · Accepted solution]

Hi， @ING_BT 

Yes, you can use function ’USERELATIONSHIP‘ to do it in a neasure.

You need to create a one-to-many  single inactive relationship between table1 and table2.

Then add a new measure 'M_cost' to replace your original filed 'Cost' in your visual table.

M_Cost = CALCULATE(SUM(Table2[Cost]),USERELATIONSHIP(Table1[Date],Table2[Date]),Table1[Users]<>0)

Result：

Best Regards,
Community Support Team _ Eason

[rohit_singh]

Hi @ING_BT ,

Please try the following : 

1) Create a calculated table using Table 1 that only has user values > 0

2) Modify relationships

Table 3 is linked to Table 2 using campaign id
Table 2 is linked to the new calculated table using the date field.

Remove all links to Table 1 (We need to use the calculated table instead)

You should get the below output

Hope this helps. Please mark this answer as the solution if it resolves your issue.

Kind regards,

Rohit