Forum Discussion

gwright15's avatar
gwright15
Helper I
7 years ago

Filtering results

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 dea...
Need Help
Tips and Tricks
gwright15's avatar
gwright15
Helper I
7 years ago

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's avatar
gwright15
Helper I
7 years ago

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])
)