Calculated table/vlookup

[Laila92]

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

[v-jayw-msft]

Hi @Laila92 ,

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/power-bi/connect-data/desktop-shape-and-combine-data#combine-querie...

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.

[sanimesa]

@Laila92You could probably create a relationship between the two tables, that might make this easier.

[Laila92]

Sorry I should have mentioned this but there is a relationship between Activity ID and the activity ID in the deals table, i will edit the question

[sanimesa]

@Laila92So 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.

[harshnathani]

@Laila92 ,

Pls share some sample data.

Regards,

HN

[Laila92]

https://docs.google.com/spreadsheets/d/1iS6VXX9r0S8hpu-TzgLnSNleF8lo_0L-mJCaLFXIRE0/edit?usp=sharing