Use Result from Intersect as a Filter Condition

[MTOnet]

Is it possible to use the result from the Intersect function as a clause within a Filter Condition?  If so, how would I go about doing this?  I haven't been able to find anything with regardsds to using a Table result as part of clause in a Filter.  I've tried using in and in values(), as well as some other options, but I havent found a way to get the result I am looking for.

I have two statements that I am trying to combine to give a final result, but I am having trouble finding the solution.  I have two data tables that I am working with, that have a common ID column and I established a 1-to-Many Relationship between Table1 and Table 2.

Here is a representation of my data.

I am trying to show (deisred result) the number Items in Table 1 of Type1 that are linked to items of Type 2 (There is more to this relationship, but this is implified for the example), that still have a most recent status of Open in Table 2.

From Table 1, I have an intersect statement that gives a table result of items of Type 2 that are linked to Type 1.  From Table 2,

I have been able to properly determine that latest status for each item and associate the result to the ID from table 1. 

I now want to combine the two, so that I can say that for any Type 1 item, how many type 2 items they are linked to have a status of Open.

So in my example data, Table1 ID 1, would have a result of 0, since the associated items, 3 & 4 have latest status value of Closed, while Table1 ID 2, will have a result of 3, since ID's 5, 6 & 7 are all in an Open Status and Table 1 ID 8 would have a result of 1 since ID 9 has a status of Closed and 10 has a status of Open.

[v-juanli-msft]

Hi @MTOnet 

Could you tell me how to create a relationship between two tables?

The "Linked to" column in Table1 is a exsiting column or a calculated column you create?

Best Regards
Maggie

[amitchandak]

I do not think I got the problem completely.

Try following.

Using Edit Query (Under Transform data in the new Office ribbon), unpivot the, separated data. Use the Split Column.

Means you should get the rows for 3 and 4 when it is 3,4

Not you can join these columns directly, by create a new table with all these id and join to both of them

Create measures to count of close and open and based on that take final call.

If possible please share a sample pbix file after removing sensitive information.Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...