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
MattAllington's avatar
MattAllington
Community Champion
7 years ago

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