Create new column that evaluates a series of conditions to be met

[Anonymous]

Hi Everyone,

So here is the problem I need help with. Ive attached a sample table of the dataset below. I need to create a column called "Linked Task" which evaluates when a Row# has all its dependencies in the "Dependency Plan" column with a "Predecessor Row#" of that plan.

Columns "Row#", "Plan" and "Task" are static.
Columns "Predecessor Row#" and "Dependency Plan" are manually populated, and therefore I need to detect outliers.

Each Row# can have none, one or more Predecessor Row#.

Each Row# can have none, one or more Dependency Plans.

A few examples of the logic the new column should have:

Row#2 should give me an output of "Linked" in the new column because the Predecessor "Row# 1" in that row is part of the Plan A marked in the Dependency column.

Row#3 should give me an output of "Linked" because both Predecessor Row# 1 and #2 are part of Plan A.

Row#4 should give me an output of "Not Linked" because I have no Predecessor Row# marked for the dependency of Plan A.

Row#5 should give me an output of "Not Linked" because Predecessor Row#3 is part of Plan A, but Predecessor Row#7 is not (it is Plan B, and I dont have Plan B in the Dependency Plan column) 

PBIX Sample Data  

Row#	Plan	Task	Precedessor Row#	Dependency Plan
1	Plan A	Task A1
2	Plan A	Task A2	1	Plan A
3	Plan A	Task A3	1, 2	Plan A
4	Plan A	Task A4		Plan A
5	Plan A	Task A5	3, 7	Plan A
6	Plan A	Task A6
7	Plan B	Task B1	2	Plan A
8	Plan B	Task B2	4, 5	Plan A
9	Plan B	Task B3	6, 12	Plan A, Plan B
10	Plan B	Task B4	5	Plan B
11	Plan B	Task B5	10	Plan B
12	Plan B	Task B6
13	Plan C	Task C1	8	Plan B
14	Plan C	Task C2	1, 10	Plan A, Plan B, Plan C
15	Plan C	Task C3	9	Plan B
16	Plan C	Task C4
17	Plan C	Task C5		Plan A
18	Plan C	Task C6	4, 8, 17	Plan A, Plan B, Plan C
19	Plan C	Task C7	4, 8, 17	Plan A, Plan B
20	Plan C	Task C8	18, 19	Plan C
21	Plan C	Task C9	12, 17	Plan B, Plan C
22		Task X	1	Plan A
23		Task Y	10	Plan B
24		Task Z	20	Plan C

I have tried creating support tables to split the information but to no avail, so Im going back to square one and see which is the best approach.

Thanks for your help,

Regards

[v-zhangti · Accepted solution]

Hi, @Anonymous 

You can try the following methods.

Measure:

Linked Task =
VAR N1 =
    CALCULATE (
        COUNT ( Predecessors[Predecessor#] ),
        FILTER (
            ALL ( 'Fact Table' ),
            [Row#] = SELECTEDVALUE ( Predecessors[Row#] )
                && [Precedessor] <> BLANK ()
        )
    )
VAR N2 =
    CALCULATE (
        COUNT ( 'Dependencies'[Value] ),
        FILTER (
            ALL ( 'Fact Table' ),
            [Row#] = SELECTEDVALUE ( 'Dependencies'[Row#] )
                && [Dependency] <> BLANK ()
        )
    )
RETURN
    IF ( N1 >= 1 && N2 >= 1, "Linked", "Not Linked" )

In your data, row 5 is 3, 4, so output Linked.

Column:

Linked Task C = 
VAR N1 =
    CALCULATE (
        COUNT ( Predecessors[Predecessor#] ),
        FILTER (Predecessors ,
            [Row#] = EARLIER('Fact Table'[Row#])
                && [Precedessor] <> BLANK ()
        )
    )
VAR N2 =
    CALCULATE (
        COUNT ( 'Dependencies'[Value] ),
        FILTER ('Dependencies' ,
            [Row#] = EARLIER('Fact Table'[Row#])
                && [Dependency] <> BLANK ()
        )
    )
RETURN
    IF ( N1 >= 1 && N2 >= 1, "Linked", "Not Linked" )

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.