Hi All, I need help for creating the below logic in PowerBI Desktop

[CG20]

I have two table Table1 & Table2, relationship between this tables is Many(Table1) to One(Table2) below is the structure & 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	FALSE	TRUE
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.

[v-jtian-msft]

Hi,@CG20 
I'll share my test data with you so you could refer to it.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-jtian-msft]

Hi,@CG20.I am glad to help you.
According to your description, if you want to realize your needs: add two columns in Table1, you can refer to my solution.
Here are my test results

I created a measure M_ to determine whether the number of different Country values is greater than or equal to 2 after the current grouping based on Name, in which I used the SUMMARIZE function to create a virtual table to return the results of the filtering.

M_ =
VAR countryOver2 =
    CALCULATE (
        DISTINCTCOUNT ( 'Table1'[Country] ),
        FILTER ( ALL ( Table1 ), 'Table1'[Name] = MAX ( 'Table1'[Name] ) )
    )
VAR nameCount =
    CALCULATE (
        COUNT ( 'Table'[Country] ),
        FILTER ( ALL ( Table1 ), 'Table1'[Name] = MAX ( 'Table1'[Name] ) )
    )
VAR _table =
    SUMMARIZE (
        'Table1',
        Table1[ID],
        Table1[Name],
        Table1[Country],
        "count",
            IF ( countryOver2 >= 2 && nameCount >= 2, 1, 0 )
    )
RETURN
    MAXX ( FILTER ( _table, 'Table1'[ID] = SELECTEDVALUE ( Table1[ID] ) ), [count] )

2. I created two calculated columns Column_1, Column_2 to show the final filtering results

Column_1 = 
VAR result =
    IF (
        'Table1'[M_] = 1,
        CALCULATE (
            VALUES ( Table2[col1] ),
            FILTER (
                ALL ( Table2 ),
                'Table2'[Country] = "All"
                    && 'Table2'[ID] = EARLIER(Table1[ID])
            )
        ),
        CALCULATE (
            VALUES ( 'Table2'[col1] ),
            FILTER (
                ALL ( Table2 ),
                'Table2'[Country] = 'Table1'[Country]
                    && 'Table2'[ID] = EARLIER(Table1[ID])
            )
        )
    )
RETURN 

    result

Column_2 = 
VAR result =
    IF (
        'Table1'[M_] = 1,
        CALCULATE (
            VALUES ( Table2[col2] ),
            FILTER (
                ALL ( Table2 ),
                'Table2'[Country] = "All"
                    && 'Table2'[ID] = EARLIER(Table1[ID])
            )
        ),
        CALCULATE (
            VALUES ( 'Table2'[col2] ),
            FILTER (
                ALL ( Table2 ),
                'Table2'[Country] = 'Table1'[Country]
                    && 'Table2'[ID] = EARLIER(Table1[ID])
            )
        )
    )
RETURN 

    result

like this:

The final results are displayed below:

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CG20]

Hello v-jtian-msft,

Thank you for your solution, I have tried it on my data but it is giving me error of Column2 as below: 

And after implementing your solution for column1, I have tried but for majority of records it is showing me blank values, below is the ss for reference

Really thankful for your solution & would appreciate your reply on this message.

Regrads
CG20

PREVIEW

[CG20]

Below is the example of data I have 

If we look for ID = 13 in Table 2 we see the values of COL1 & COL2 it is TRUE & TRUE  for all the countries including the ALL value.
But from your logic in Table1, Check1 column we are getting blanks / no values in Table1 for all the rows we should get TRUE value as in Country value from Table2 for ALL is TRUE.

Note: And we had data for multiple versions in Table1 like you can see & the overall data of Table1 is in millions. 

If there are any queries do reply.

Thanks & Regards,

CG20