Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
uif19085
Helper III
Helper III

DAX Column if all links are implemented

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

 

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

 

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

 

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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
    )

vyiruanmsft_0-1679884086488.png

Best Regards

View solution in original post

2 REPLIES 2
uif19085
Helper III
Helper III

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
Not applicable

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
    )

vyiruanmsft_0-1679884086488.png

Best Regards

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors