Filtering results

[gwright15]

Morning all

i have a basic query, but can't get my head around it.

We have 4 four tables - a date table, an activity table, deals table and a user table.

Every activity is linked to a deal.  I would like to calculate the value of deals where an activity has table place.  The table relationships like as follows:

The result would be a total per user who completed the activity - i.e. example a has x number of deal with a value of y.

Can you help me?

[MattAllington]

Firstly, thanks for sharing the model view. Some observations

1. You’re User table does not filter the deals table

2. There is a 1 to many relationship between deals and activities. 

Based on 2, even if you turn relationship 2 to be bidirectional, that will make users filter deals (to get value), but value will be multiplied for each instance of an activity. 

My guess is. 

=calculate(sumx(deals,deals[value]),activities)

but you would have to test it

[gwright15]

Thanks Matt!

In may main model, the User table does link to the deal table, but for this particular measure, i don't want to take into account the user allocated to the deal, only who completed the activity.  I understand this will lead to some strange data, as two users could work on the same deal, but this is find for this use case.

The 1 to many relationship seems correct (correct me if i'm wrong), but i think you're right that the relationship needs to be bi-directional.  

One element i didn't mention in the first query is if a single user had two activities for a deal, worth say £1k, i only want to return £1k, rather than £2k.

I've tried your measure, but it seems to come back with any error message " Too many argument" etc.

I have found a formula which seems to work, but not sure if this the best method:

TotalValue = 
Calculate (
    Sum(Deals[Value]),
    Filter(Deals,Deals[Deal ID] In VALUES(Activities[Deal ID]))
)

Any helps would be amazing!

[gwright15]

In my actual tables, i have multi realationship, and as such, some for inactive.

This formula works.

TotalValue = 
Calculate (
    Sum(Deals[Value]),
    Filter(Deals,Deals[Deal ID] In VALUES(Activities[Deal ID]))
)

But if i make the relationships inactive, and use the "USERELATIOSHIP" formala instead, it stops working - see below.

TotalValue = 
Calculate (
    Sum(Deals[Value]),
    Filter(Deals,Deals[Deal ID] In VALUES(Activities[Deal ID])),
    USERELATIONSHIP(Activities[Deal ID],Deals[Deal ID]),
    USERELATIONSHIP('Date'[Date],Deals[Add Date]),
    USERELATIONSHIP(Activities[User ID],Users[User ID])
)