Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
68 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |