Adding a new derived column to an existing table from a related table

[Anonymous]

I have two tables-

Table 1

“Id”                      “Values”

1                           1           

1                           0

1                           1

2                           1

2                           1

3                           1

4                           0

4                           1

4                           1

Table 2

Id                         Status

1                           No

2                           Yes

3                           Yes

4                           No

I need to add a column to Table 2, Status such that Status = Yes if all Values are 1, else “No”.

Can someone please help me out?

[ChrisMendoza · Accepted solution]

@Anonymous -

Does this work for you?

Column = 
VAR t1Rows =
    CALCULATE(
        COUNTROWS(Table1),
        FILTER(
            Table1,
            Table1[Id] = Table2[Id]
        )
    )

VAR t1Count1 =
    CALCULATE(
        SUM(Table1[Values]),
        FILTER(
            Table1,
            Table1[Id] = Table2[Id]
        )
    )

RETURN 
    IF(
        t1Rows - t1Count1 = 0,
        "Yes",
        "No"
    )

[Anonymous]

Thanks you, @ChrisMendoza. It worked!