- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

DAX Column if all links are implemented
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.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hello, @v-yiruan-msft 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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
04-27-2024 03:07 AM | |||
08-01-2024 12:49 AM | |||
05-13-2024 03:18 AM | |||
03-27-2024 10:00 AM | |||
07-29-2024 03:00 PM |
User | Count |
---|---|
132 | |
105 | |
86 | |
55 | |
46 |