DAX Column if all links are implemented

[uif19085]

I am facing the following challenge in DAX, I created some mock tables to help illustrate.

ID	UniqueID	Implementation	Calculated Column
1	AA	Yes	0
2	BB	No	1
3	CC	No	0
4	DD	Yes	0
5	EE	No	0
6	FF	Yes	0
7	GG	Yes	0
8	HH	Yes	1
9	II	No	0
10	JJ	No	1
11	KK	Yes	1
12	LL	Yes	0

ID	LinkedID
1	II
1	FF
2	DD
2	AA
3	HH
3	BB
5	CC
7	II
8	KK
8	DD
9	FF
9	BB
10	LL
10	HH
10	FF
11	HH
11	LL

The second table acts as a "dictionary" that connects rows from Table 1 to other rows from Table 1.
For example:

- ID 1 can be found twice in table 2. Once linked with "II" and a second time linked with "FF" (UniqueId -> LinkedId).
This means that ID 1 from the first table is asociated with IDs 9 and 3, respectively.
 Next, values "II" and "FF" are having an Implementation value which will define if ID 1 is implemented or not, the condition is that all this linked values have to be implemented, that means equals to "Yes". 
Example Calculation: 
For row ID=1 (AA), we can use Table 2 to conclude that "AA" is connected to "II" and "FF" and the values for "Implementation" contain "No" for "II" and "Yes" for "FF". The result for this is 0, beacause one  link is "No", we should  have all links  with "Yes"
For row ID=2 (BB), we can use Table 2 to conclude that "BB" is connected to "DD" and "AA" and the values for "Implementation" contain "Yes" for "DD" and "Yes" vor "AA". The result for this is 1, beacause both are "Yes".

I'm looking for a calculated column that will mark with 1 the IDs that are having all links implemented.

[uif19085]

Hello, @Anonymous and thank you for your answer. I tried this for three time already and everytime it gets stuck to "Working on it", the RAM doesn't seems to incresed in size too much. 

[Anonymous · Accepted solution]

Hi @uif19085 ,

You can create a calculated column as below to get it, please find the details in the attachment.

Column =
VAR _linkids =
    CALCULATETABLE (
        VALUES ( 'Table 2'[LinkedID] ),
        FILTER ( 'Table 2', 'Table 2'[ID] = 'Table 1'[ID] )
    )
VAR _count =
    COUNTROWS ( _linkids )
VAR _impcount =
    CALCULATE (
        COUNT ( 'Table 1'[Implementation] ),
        FILTER (
            'Table 1',
            'Table 1'[UniqueID]
                IN _linkids
                && 'Table 1'[Implementation] = "Yes"
        )
    )
RETURN
    IF (
        _count = _impcount
            && NOT ( ISBLANK ( _count ) ) && NOT ( ISBLANK ( _impcount ) ),
        1,
        0
    )

Best Regards