Forum Discussion
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. 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
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" )
2 Replies
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.
