Calculated column based on multiple criteria from another table

[Kruz_R]

Hello,

I hope someone can assist or point me toward a relevant post.

I am struggling to create a calculated column Table 1[READY] that meets criteria from Table 2 [Prof] and [Current] columns. Due to an "Active" relationship between other tables in the data model, an "Inactive Relationship" could be made between the two Employee Columns.

What I am hoping to achieve is the result for the Table 1 [READY] column are Prof 1 & 2 & 3 = "Y" && Prof 4 || 5 = "Y" then "Yes", else "No" (or a 1 or 0) which ever is easiest.

Table 1

Employee	Ready
1	No
2	Yes
3	No
4	Yes
5	No
6
7

TABLE 2

Employee	Prof	Current
1	1	y
1	2	y
1	3	y
1	4	n
1	5	n
2	1	y
2	2	y
2	3	y
2	4	y
2	5	n
3	1	n
3	2	y
3	3	n
3	4	y
3	5	n
4	1	y
4	2	y
4	3	y
4	4	n
4	5	y
5	1	y
5	2	y
5	3	y
5	4	y
5	5	n
6	1	n
6	2	y
6	3	y
6	4	n
6	5	y
7	1	y
7	2	y
7	3	y
7	4	y
7	5	n

Happy for any suggestions and can provide additional information if required.

Thanks in advance.

Kruz

[danextian]

Hi @Kruz_R ,

Try this as a calculated column in table 1

test =
VAR __1 =
    CALCULATE (
        MAX ( T2[Current] ),
        FILTER ( T2, T2[Prof] = 1 && T2[Employee] = EARLIER ( T1[Employee] ) )
    )
VAR __2 =
    CALCULATE (
        MAX ( T2[Current] ),
        FILTER ( T2, T2[Prof] = 2 && T2[Employee] = EARLIER ( T1[Employee] ) )
    )
VAR __3 =
    CALCULATE (
        MAX ( T2[Current] ),
        FILTER ( T2, T2[Prof] = 3 && T2[Employee] = EARLIER ( T1[Employee] ) )
    )
VAR __4 =
    CALCULATE (
        MAX ( T2[Current] ),
        FILTER ( T2, T2[Prof] = 4 && T2[Employee] = EARLIER ( T1[Employee] ) )
    )
VAR __5 =
    CALCULATE (
        MAX ( T2[Current] ),
        FILTER ( T2, T2[Prof] = 5 && T2[Employee] = EARLIER ( T1[Employee] ) )
    )
RETURN
    IF (
        __1 = "y"
            && __2 = "y"
            && __3 = "y"
            && ( __4 = "y"
            || __5 = "y" ),
        "Yes",
        "No"
    )

Note: only an inactive relationship exists between the two table.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new column.

Ready CC =
VAR _category =
    FILTER ( Table2, Table2[Employee] = EARLIER ( Table1[Employee] ) )
RETURN
    IF (
        MAXX ( FILTER ( _category, Table2[Prof] = 1 ), Table2[Current] ) = "y"
            && MAXX ( FILTER ( _category, Table2[Prof] = 2 ), Table2[Current] ) = "y"
            && MAXX ( FILTER ( _category, Table2[Prof] = 3 ), Table2[Current] ) = "y"
            && OR (
                MAXX ( FILTER ( _category, Table2[Prof] = 4 ), Table2[Current] ) = "y",
                MAXX ( FILTER ( _category, Table2[Prof] = 5 ), Table2[Current] ) = "y"
            ),
        "Yes",
        "No"
    )