Supplies are limited. Contact info@espc.tech right away to save your spot before the conference sells out.
Get your discountScore big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount
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.
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
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.
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