Creating New Table by two tables and with filter conditions

[NMahi1703]

Hi Team,

I need to create a table with two tables data by appyind the conditional filers as below.

Table1

Account ID	ID
3630	1811
7625	3291
7625	5567

Table2

Account Number	BilliD	Name
3630	NA	P1
3630	NA	P2
3630	NA	P3
3630	NA	P4
7625	3291	P5
7625	3291	P6
8888	3291	P7
7625	4691	P8
7625		P9
7625		P10
7625		P11

Above table has relationship as Table1 Account ID with Table2 of Account Number. 
Conditions to Filter Data:

1. Table1 ID is matched with Table2 BilliD then Account Number Product should map with BillId Name
2. If Table1 ID is not matched or found in Table3 BilliD then Name Rows Assoisated with Account Number Name's Should Display in Table.

OutPut:

ID	Name
1811	P1
1811	P2
1811	P3
1811	P4
3291	P5
3291	P6
5567	P5
5567	P6
5567	P8
5567	P9
5567	P10
5567	P11

Thanks

[NMahi1703]

Hi @Jihwan_Kim ,

Thanks for solution but still i'm stuck with another problem with your code.
My Table2 has follwing Data

Account Number	BilliD	Product Name	Version	Acc Status
3630	NA	P1	1	Warning
3630	NA	P2	2	Normal
3630	NA	P3	2	Normal
3630	NA	P4	10	Normal
7625	3291	P5	2	Warning
7625	3291	P6	9	Warning
8888	3291	P7	4	Critical
7625	4691	P8	2	Warning
7625		P9	6	Normal
7625		P10	7	Critical
7625		P11	8	Normal

Here I have each Account Status Column Value may be Normal , Warning , Critical. I want to create a Staus Column as  highiest  Status value realted to each ID
Status Order is Critical , Warning , Normal.
Desired Outupt is 

ID	Name	Status	Acc Status
1811	P1	Warning	Warning
1811	P2	Warning	Normal
1811	P3	Warning	Normal
1811	P4	Warning	Normal
3291	P5	Warning	Warning
3291	P6	Warning	Warning
5567	P5	Critical	Warning
5567	P6	Critical	Warning
5567	P8	Critical	Warning
5567	P9	Critical	Normal
5567	P10	Critical	Critical
5567	P11	Critical	Normal

Thanks.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below formula and the attached pbix file.

New Table =
VAR _statustable =
    DATATABLE (
        "status", STRING,
        "index", INTEGER,
        {
            { "Critical", 1 },
            { "Warning", 2 },
            { "Normal", 3 }
        }
    )
VAR _resulttable =
    SUMMARIZE (
        GENERATE (
            Table1,
            FILTER (
                Table2,
                IF (
                    COUNTROWS ( FILTER ( Table2, Table2[BilliD] = Table1[ID] ) ) > 0,
                    Table2[BilliD] = Table1[ID]
                        && Table2[Account Number] = Table1[Account ID],
                    Table2[Account Number] = Table1[Account ID]
                )
            )
        ),
        Table1[ID],
        Table2[Name],
        Table2[Acc Status]
    )
RETURN
    ADDCOLUMNS (
        _resulttable,
        "@Status",
            MAXX (
                FILTER (
                    _statustable,
                    [index]
                        = MINX (
                            FILTER (
                                _statustable,
                                [status]
                                    IN SUMMARIZE (
                                        FILTER ( _resulttable, Table1[ID] = EARLIEST ( Table1[ID] ) ),
                                        Table2[Acc Status]
                                    )
                            ),
                            [index]
                        )
                ),
                [status]
            )
    )

[NMahi1703]

Hi @Jihwan_Kim ,

Thanks for you solution,it's working and failing with one senarios as mentioned below kindly look into this issue.

[NMahi1703]

Hello @Jihwan_Kim , 

Your Solution is not working for if i have Mutiple Account Id's are assosiated with Same ID. Then it's not working kindly help me with solution
Table 1: 

Account ID	ID
3630	1811
7625	3291
7625	5567
3631	5567

Table 2: 

Account Number	BilliD	Product Name	Version	Acc Status
3630	NA	P1	1	Warning
3630	NA	P2	2	Normal
3630	NA	P3	2	Normal
3630	NA	P4	10	Normal
7625	3291	P5	2	Warning
7625	3291	P6	9	Warning
8888	3291	P7	4	Critical
7625	4691	P8	2	Warning
7625		P9	6	Normal
7625		P10	7	Critical
7625		P11	8	Normal
3631	5567	P12	7	Normal
3631	5567	P13	10	Normal
3631		P14	5	Critical

Kindly Provide the Solution

[NMahi1703]

Hi @Jihwan_Kim ,

Kindly provide the solution fo this solutions.

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

New Table = 
SUMMARIZE (
    GENERATE (
        Table1,
        FILTER (
            Table2,
            IF (
                COUNTROWS ( FILTER ( Table2, Table2[BilliD] = Table1[ID] ) ) > 0,
                Table2[BilliD] = Table1[ID]
                    && Table2[Account Number] = Table1[Account ID],
                Table2[Account Number] = Table1[Account ID]
            )
        )
    ),
    Table1[ID],
    Table2[Name]
)