Matching data from two tables not with lookupvalue as there is more than one result

[Anonymous]

I have two table that I want to do a lookup, but because lookup value only works when on expected value is in the table. This data set however have multipe. Is there any way to do a lookup to have an out put like this.

Table 1
No.	Name	Number
1	AB	1234
23	ABCD	100
123	DFSD	1234
444	EFS	422
Table 2
No.	Name	Number
12541353	SADSAD	1234
12341423	DAADFD	100
14345212	DAFSASD	1234
New table 1
No.	Name	Number	No. Table 2	Name Table 2
1	AB	1234	12541353	SADSAD
1	AB	1234	14345212	DAFSASD
23	ABCD	100	12341423	DAADFD
123	DFSD	1234	12541353	SADSAD
123	DFSD	1234	14345212	DAFSASD
444	EFS	422

[Anonymous]

@tamerj1thanks again this works briliant. Is it possible to add another filter function to that it is looking for two matches to be true as part of this? -

 FILTER ( Table2, Table2[Number] = Table1[Number] ),

[tamerj1 · Accepted solution]

Hi @Anonymous 
Here is a sample file with the solution https://www.dropbox.com/t/2JpsLFnnm9GrWpHL

Table3 = 
UNION (
    GENERATE ( 
        Table1, 
        SELECTCOLUMNS (
            FILTER ( Table2, Table2[Number] = Table1[Number] ),
            "@Name", Table2[Name],
            "@No .", Table2[No.]
        )
    ),
    ADDCOLUMNS (
        FILTER (
            Table1,
            NOT ( Table1[Number] IN VALUES ( Table2[Number] ) )
        ),
        "@Name", "",
        "@No .", ""
    )
)

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below DAX formula.

It is for creating a new table.

New_Table = 
VAR _nonumberlistintable2 =
    EXCEPT ( VALUES ( Table1[Number] ), VALUES ( Table2[Number] ) )
VAR _filtertable1 =
    ADDCOLUMNS (
        FILTER ( Table1, Table1[Number] IN _nonumberlistintable2 ),
        "No1.", BLANK (),
        "Name1", BLANK (),
        "Number1", BLANK ()
    )
RETURN
    UNION (
        GENERATE ( ALL ( Table1 ), FILTER ( Table2, Table2[Number] = Table1[Number] ) ),
        _filtertable1
    )

[tamerj1]

Hi @Anonymous 

can you build relationships between the two tables? Are trying to create a new calculated table or just a table visual?

[Anonymous]

A new calculated table