Filtering Data in Another Table which has the relationship with One table with Mutiple Conditions

[NMahi1703]

Hi Team,

I Have One table Name Table_1 below 

Account ID	ID
9484	1811
9484	3291
9484	4691

Another Table as Table_2

Account Number	BilliD	Name
9484	1811	P1
9484	3291	P2
9484	NA	P3
9484	NA	P4

I have the realtion between Table1 to Table2 with Account Id and Account Number.
My requirment is if Id  in table is maching with Billid in Table2 then that prodcut has to display.If There is no Match found then Need to display all the Name's in Table 
Output:

ID	Account NO	Name
1811	9484	P1
3291	9484	P3
4691	9484	P1
4691	9484	P2
4691	9484	P3
4691	9484	P4

Kindly Help Me. 

[NMahi1703]

Hi @Anonymous ,

Thanks for you help , small change in the condition and data could you please help me out with below senario.
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

Now here as mentioned above account id and Account Number has an many to many relationship.Desiried output is 

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

[Anonymous · Accepted solution]

HI @NMahi1703,

You can create a new table with crossjoin function with table 1 field and table 2 Name fields, then add a filter to remove not match records based on the raw table records mapping.

Expand = 
VAR idList =
    CALCULATETABLE (
        VALUES ( Table2[BilliD] ),
        FILTER ( Table2, [BilliD] <> "NA" )
    )
VAR mapping =
    SELECTCOLUMNS (
        FILTER ( Table2, [BilliD] <> "NA" ),
        "Merged",
            [Account Number] & "-" & [BilliD] & "-" & [Name]
    )
RETURN
    FILTER (
        CROSSJOIN ( Table1, VALUES ( Table2[Name] ) ),
        IF (
            [ID] IN idList,
            [Account ID] & "-" & [ID] & "-" & [Name] IN mapping,
            TRUE ()
        )
    )

Regards,

Xiaoxin Sheng