In PowerBI, how to calculate column values?

Question

I have two tables Table1 & Table2, relationship between these tables is many (Table1) to one (Table2). This is the structure & sample data of the tables

Table1:

ID  Name    Country
--------------------
1   Aaa     IND
1   Aaa     USA
2   Bbb     FRA
3   Ccc     BEL
3   Ccc     BEN
3   Ccc     ARE
3   Ccc     AUS

Table2:

ID  Name    Country     col1    col2
-------------------------------------
1   Aaa     IND         TRUE    TRUE
1   Aaa     All         FALSE   TRUE
2   Baa     FRA         FALSE   TRUE
2   Baa     All         FALSE   TRUE
3   Ccc     BEL         TRUE    TRUE
3   Ccc     AUS         TRUE    FALSE
3   Ccc     All         TRUE    TRUE

Expected output (need new calculated columns in Table1 output as follows)

ID  Name    Country     T        F
--------------------------------------
1   Aaa     IND        FALSE    TRUE
1   Aaa     USA        FALSE    TRUE
2   Bbb     FRA        FALSE    TRUE
3   Ccc     BEL        TRUE     TRUE
3   Ccc     BEN        TRUE     TRUE
3   Ccc     ARE        TRUE     TRUE
3   Ccc     AUS        TRUE     TRUE

Scenario is when distinct id eg(1) has two rows with different countries in table1, it will look for the same id(1) in table2.

And in table2's country column we have "ALL" value then the values from column T & F should populate for both the rows in Table1 in new columns.

Thank you for reading & helping in advance.

Answer 1

you can try this to create columns

T =
IF (
    ISBLANK (
        MINX (
            FILTER (
                Table2,
                Table1[ID] = Table2[ID]
                    && Table2[Country] = "All"
                    && Table2[col1] = TRUE ()
            ),
            Table1[ID]
        )
    ),
    FALSE (),
    TRUE ()
)
F =
IF (
    ISBLANK (
        MINX (
            FILTER (
                Table2,
                Table1[ID] = Table2[ID]
                    && Table2[Country] = "All"
                    && Table2[col2] = TRUE ()
            ),
            Table1[ID]
        )
    ),
    FALSE (),
    TRUE ()
)