Forum Discussion
Solved
Calculated column based on multiple criteria from another table
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...
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" )
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.