Lookup Value from another Table

[D92]

Hi Guys,

New to the forum and I would  really appreciate some help.

I have two data source , with different values, of of the data source contains calculated column to return the values from the second data source .For the sake of simplicity will call them A and B.Including sample data.

Table A Sample 

Software	Manufacturer	Vulnerable Manufacturer Found	Vulnerable Software found	URL
biosdevname	Others
kpartx	Others
libXtst	Others
hwdata	Others

Table B sample 

Entry ID	Manufacturer	Software	Version	Type	Platform	Date
https://vuldb.com/?id.207465	TOTOLINK	empty	4.1.2cu.5182_B20201026	empty	empty	08-29-09:20
https://vuldb.com/?id.207464	TOTOLINK	empty	4.1.2cu.5185_B20201128	empty	empty	08-29-09:20
https://vuldb.com/?id.207463	TOTOLINK	empty	4.1.2cu.5182_B20201027	empty	empty	08-29-09:20

Vulnerable Software Found and Vulnerable Manufacturer found , are calculated column , which return value from Table B respectively using the following formula :

MAXX (
    FILTER ( vuldb_id_report_no_version, SEARCH ( vuldb_id_report_no_version[Software], bov_software[Software], 1, 0 ) > 0 ),
    [Software] 
)

What i would like to achieve is return the value in the URL column , based on if vulnerable manufacturer or vulnerable software is true.

IF (ISBLANK(bov_software[Vulnerable Manufacturer Found] && bov_software[Vulnerable Software found] ),
MINX(
 FILTER ( vuldb_id_report_no_version, SEARCH ( vuldb_id_report_no_version[URL], 1, 0 ) > 0 ),
 [URL]

))

Your help is greatly appreciated

[ryan_mayu]

@D92 

maybe you can try this

IF (ISBLANK(bov_software[Vulnerable Manufacturer Found]）) || isblank( bov_software[Vulnerable Software found] )),
MINX(
 FILTER ( vuldb_id_report_no_version, SEARCH ( vuldb_id_report_no_version[URL], 1, 0 ) > 0 ),
 [URL]

))

[D92]

@ryan_mayu  

Thank you for the suggestion that did not work.

[ryan_mayu]

@D92 

I checked your sample data

we don't have the sample value for table A and table B, how can we create the relationship or get value from table B to table A?

[D92]

@ryan_mayu , can the data be extracted from table B to Table A if a value in the calculated columns exist ?

[ryan_mayu]

sry, i found a typo error in my last reply. We don't have same value in table A and table B. So I don't think we can get the value for last three colsumn in the first row of your sample data.

I think we can get from table B to table A by using the calculated value. could you pls provide update sample data or explain the detailed logics?

[ryan_mayu]

could you pls provide the new sample data and the expected output?

based on the sample data you provided, i can't get the result since we can't use software to find the related info in table B

[D92]

@ryan_mayu my post are getting deleted when I try to paste the sample.

I would like to have the calculated column in Table A URL , obtain the information from Table B Entry ID  only if value in Vulnerable Software Found or Vulnerable Manufacturer Found is populated.

Example from the expected outcome.

Software	Manufacturer	Vulnerable Manufacturer Found	Vulnerable Software Found	URL
Microsoft Silverlight	Microsoft Corporation	Microsoft Silverlight	Microsoft Corporation	https://vuldb.com/?id.207623

[ryan_mayu]

pls try if lookupvalue works for you

LOOKUPVALUE function (DAX) - DAX | Microsoft Docs

LOOKUPVALUE(
TableB[URL],
TableB [vunlerable manufacuturer Found].
TableA [vunlerable manufacuturer Found].
TableB [Vulnerable Souftware Found].

Table A[[Vulnerable Souftware Found])

[D92]

@ryan_mayu 

Not sure if this was your suggestion , here is what i have tried which did not return any value.

URL = 
LOOKUPVALUE(vuldb_id_report_no_version[URL],
vuldb_id_report_no_version[Manufacturer],bov_software[Vulnerable Manufacturer Found],vuldb_id_report_no_version[Software],bov_software[Vulnerable Software found]
)

Table A Sample

Software	Manufacturer	Vulnerable Manufacturer Found	Vulnerable Software Found	URL
Microsoft Silverlight	Microsoft Corporation
Realtek High Definition Audio Driver	Realtek Semiconductor Corp.
Update for Windows 10 for x64-based Systems (KB4023057)	Microsoft Corporation
Microsoft Update Health Tools	Microsoft Corporation
Grammarly for Windows	Others
Microsoft Azure Information Protection	Microsoft Corporation
Microsoft Visual C++ 2008 Redistributable - x86 9.0.30729.4974	Microsoft Corporation
Microsoft SQL Server 2008 R2 Management Objects	Microsoft Corporation
Enterprise Communicator	CA
GNOME CORBA ORB and component framework	Oracle Corporation
Xmanager 6	NetSarang Computer, Inc.
JDK 8.0 64-bit Runtime Env. (1.8.0_311)	Oracle America, Inc
Microsoft Edge	Microsoft Corporation
Nessus Agent (x64)	Tenable, Inc.
Oracle - OraClient19Home1	Others
Oracle - OraClient11g 11.2.0	Others

 Table B sample 

Entry ID	Manufacturer	Software	Version	Type	Platform	Date
https://vuldb.com/?id.207464	TOTOLINK	empty	4.1.2cu.5185_B20201128	empty	empty	08-29-09:20
https://vuldb.com/?id.207463	TOTOLINK	empty	4.1.2cu.5182_B20201027	empty	empty	08-29-09:20
https://vuldb.com/?id.207462	TOTOLINK	empty	4.3.0cu.7647_B20210106	empty	empty	08-29-09:20
https://vuldb.com/?id.207461	TOTOLINK	empty	4.1.2cu.5204_B20210112	empty	empty	08-29-09:20
https://vuldb.com/?id.207460	TOTOLINK	empty	4.1.2cu.5137_B20200730	empty	empty	08-29-09:21
https://vuldb.com/?id.207459	TOTOLINK	empty	4.1.5cu.532_B20210610	empty	empty	08-29-09:21

[ryan_mayu]

@D92 

let's clarify the logic

This is the first row of table A

Software	Manufacturer	Vulnerable Manufacturer Found	Vulnerable Software Found	URL
Microsoft Silverlight	Microsoft Corporation

How do you want to get the value for the last 3 columns from Table B?

in my opinion, based on the sample data you provided, we can't get the expected output below.

Software	Manufacturer	Vulnerable Manufacturer Found	Vulnerable Software Found	URL
Microsoft Silverlight	Microsoft Corporation	Microsoft Silverlight	Microsoft Corporation	https://vuldb.com/?id.207623

[D92]

@ryan_mayu 

Table A contains calculated values for Vulnerable software found and Vulnerable Manufacturer Found based on the following formula :

MAXX (
    FILTER ( vuldb_id_report_no_version, SEARCH ( vuldb_id_report_no_version[Software], bov_software[Software], 1, 0 ) > 0 ),
    [Software] 
)

 Therefore they are calculated values which are filtered from Table B and A.

What I want to achieve is if they are populated and not blank , get the Entry ID from table B and insert it in Table A .Hope this makes it more clear now.

Tried the bellow formula with no success.

URL = IF(NOT(ISBLANK(bov_software[Vulnerable Manufacturer Found] || bov_software[Vulnerable Software found])),
FILTER(vuldb_id_report_no_version,SEARCH(vuldb_id_report_no_version[URL],vuldb_id_report_no_version[URL]))

)

[ryan_mayu]

i tried your DAX and it returns null.

maybe you can try the pbix file attached or you can provide your pbix file based on the sample data.

[D92]

@ryan_mayu 

I tried the proposed soluting however , the URL column didnt return a value. I was unable to upload the pbix file unfortunetly.