Forum Discussion
Calculated table/vlookup
I am struggling with the following:
I have one table (1) with deal ID's and data about related activities (one deal ID can have multiple activity IDs).
I have another table (2) with deal IDs and data about the deals, i do also have the activity ID here so it has a relationship with table 1.
Now I want to filter out the deal IDs from the first table that have activity type = DEMO, activity creator = x,y or z (and possibly a few more criteria). I then want the second table to take those filtered deal IDs and filter out the deal IDs that are WON.
(So I want all the won deals that have an activity of type = DEMO and activity creator = x,y or z).
I have been trying to create virtual tables and summarizing them in different ways, taking VALUES from the first (filtered) table and then SUMMARIZING it with the second table but to no avail.
Example data https://docs.google.com/spreadsheets/d/1iS6VXX9r0S8hpu-TzgLnSNleF8lo_0L-mJCaLFXIRE0/edit?usp=sharing
6 Replies
AnonymousYou could probably create a relationship between the two tables, that might make this easier.
AnonymousSo it looks like you want to pick deals that were won (plus additional selection criteria) which have an activity of type demo.
lookupvalue will not work since it could return multiple values. One option is to add a relationship between the deal ids (it will be inactive) and then create a count measure that counts the number of activities for the deal with type demo, you will need to use Userelationship for this.
Hi Anonymous ,
I did't find the activity creator and the values you want to summarized in your sample data thus i couldn't image the expected result you want. However, i do think about if we could use Merge table feature or CROSSJOIN() function here.
https://docs.microsoft.com/en-us/dax/crossjoin-function-dax
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
