topic Re: Matching data from two tables not with lookupvalue as there is more than one result in DAX Commands and Tips

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

Anonymous — Fri, 22 Jul 2022 04:11:23 GMT

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

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

tamerj1 — Fri, 22 Jul 2022 04:42:09 GMT

Hi Anonymous

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

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

Anonymous — Fri, 22 Jul 2022 05:04:09 GMT

A new calculated table

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

Jihwan_Kim — Fri, 22 Jul 2022 05:30:54 GMT

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 )

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

tamerj1 — Fri, 22 Jul 2022 08:57:25 GMT

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 .", "" ) )

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

Anonymous — Thu, 28 Jul 2022 01:07:43 GMT

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] ),