Value in table 1 exist/does not exist in table 2 for each entity

[PowerBeeEye]

I have 3 tables:

Servers

Server_Name		Server_OS
Server_1		Windows Server 2016
Server_2		Windows Server 2019
Server_3		Windows Server 2016

All Updates

Server_Name		Patch_ID
Server_1		KB123
Server_1		KB456
Server_2		KB123

Critical_Patch

OS		KB_ID
Windows Server 2016		KB456
Windows Server 2019		KB789

I would like to have the conditional column like so

Servers

Server_Name		Server_OS		Critical_Patch_Installed
Server_1		Windows Server 2016		Yes
Server_2		Windows Server 2019		No
Server_3		Windows Server 2016		No

Essentially, For each server in SERVERS,

if list of all KB_IDs in ALL UPDATES for matching [Server_Name] contains mathing [KB_ID] in CRITICAL_PATCH for matching [OS]then [Critical_Patch_Installed] = "yes"

Really appreciate the help!

[Fowmy]

@PowerBeeEye 

Please check this solution, I did not create any relationships between Tables.

Critical_Patch_Installed = 

VAR _UPDATE = 
SELECTCOLUMNS(
FILTER(
	GENERATE(
		'All Updates',
		Critical_Patch
	),
	'All Updates'[Patch_ID] = Critical_Patch[KB_ID]
),
"_SERVER", 'All Updates'[Server_Name]
)

RETURN
IF( 
    SELECTEDVALUE(Servers[Server_OS]) IN _UPDATE,
    "Yes",
    "No"
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[PowerBeeEye]

@Fowmy 

Sorry, but I don't think that worked. Perhaps I made a mitake with the column names, but here are the tables once again:

CVE-2020-1350

KBID	OS
KB4565541	Windows Server 2012 R2
KB3000483	Windows Server 2016
KB4558998	Windows Server 2019

Q_Domain_Controllers_Latest

operatingsystem	dnshostname
Windows Server 2016	Server1.domain.ext
Windows Server 2019	Server2.domain.ext
Windows Server 2012 R2	Server3.domain.ext

Q_Domain_Controllers_Hotfix_Latest

HotFixID	ComputerDNS
KB3000483	Server1.domain.ext
KB3003057	Server1.domain.ext
KB3011780	Server1.domain.ext
KB3019978	Server2.domain.ext
KB3023266	Server2.domain.ext
KB3035126	Server2.domain.ext
KB3045685	Server3.domain.ext
KB3045999	Server3.domain.ext
KB4565541	Server3.domain.ext

What I'm looking for:

Q_Domain_Controllers_Latest

operatingsystem	dnshostname	CC_DNSVulnerabilityPatchInstalled
Windows Server 2016	Server1.domain.ext	Yes
Windows Server 2019	Server2.domain.ext	No
Windows Server 2012 R2	Server3.domain.ext	Yes

[v-yiruan-msft]

Hi @PowerBeeEye ,

You can create a measure as below:

CC_DNSVulnerabilityPatchInstalled =
VAR _patches =
    CONCATENATEX (
        FILTER (
            ALLSELECTED ( 'Q_Domain_Controllers_Hotfix_Latest' ),
            'Q_Domain_Controllers_Hotfix_Latest'[ComputerDNS]
                = MAX ( 'Q_Domain_Controllers_Latest'[dnshostname] )
        ),
        'Q_Domain_Controllers_Hotfix_Latest'[HotFixID],
        ","
    )
VAR _npatches =
    SUBSTITUTE ( _patches, ",", "" )
VAR _index =
    ROUND ( LEN ( _npatches ) / 9, 0 ) - 1
VAR mytable =
    ADDCOLUMNS (
        GENERATESERIES ( 0, _index ),
        "mylist",
             (
                MID ( _npatches, [Value] * 9 + 1, 9 )
            )
    )
VAR mylist =
    SELECTCOLUMNS ( mytable, "list", [mylist] )
VAR _a =
    CALCULATE (
        DISTINCTCOUNT ( 'CVE-2020-1350'[KBID] ),
        FILTER (
            ALLSELECTED ( 'CVE-2020-1350' ),
            'CVE-2020-1350'[KBID] IN mylist
                && 'CVE-2020-1350'[OS] = MAX ( 'Q_Domain_Controllers_Latest'[operatingsystem] )
        )
    )
RETURN
    IF ( _a > 0, "Yes", "No" )

Best Regards

Rena

[PowerBeeEye]

thank you @v-yiruan-msft 

One issue though - the column 'CVE-2020-1350'[OS] does not contain unique values:

KBID	OS
KB4565524	Windows Server 2008 R2
KB4565539	Windows Server 2008 R2
KB4565537	Windows Server 2012
KB4565535	Windows Server 2012
KB4565541	Windows Server 2012 R2
KB4565540	Windows Server 2012 R2
KB4565511	Windows Server 2016
KB4558998	Windows Server 2019
KB4559003	Windows Server 2019

[v-yiruan-msft]

Hi @PowerBeeEye ,

Please try to set the cardinality of relationship between table CVE-2020-1350 and table Q_Domain_Controllers_Latest as Many to One and check if the measure "CC_DNSVulnerabilityPatchInstalled" still works well. Any concern or problem please feel free to let me know.

Best Regards

Rena

[PowerBeeEye]

@v-yiruan-msft 

Unfortunately, that would not work since neither of the columns have only unique values; both the 

OperatingSystem column in the Q_Domain_Controllers_Latest table, and 

OS column in CVE-2020-1350 table have repeating values.

Here is a list of unique values:

• CVE-2020-1350 [KBID]
• Q_Domain_Controllers_Latest [dnshostname]

[v-yiruan-msft]

Hi @PowerBeeEye ,

Here I will share my sample pbix file with you. Could you please help to check if I missed any information that caused the measure can't work well in your side ? Thank you. About the problem of duplicated values the OperatingSystem column in Q_Domain_Controllers_Latest table and OS column in CVE-2020-1350 table, the cardinality of relationship between them has be set as Many to Many in my sample pbix fie.

Best Regards

Rena

[Fowmy]

@v-yiruan-msft 

Hi did you try my solution?

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[PowerBeeEye]

@Fowmy  I think I got it working - I am verifying the output.

One question - how can I use the measure in a visualizer? I would like to show how many servers have "yes" and how many have "no".

[Fowmy]

@PowerBeeEye 

You can download the file: HERE

To visualize the count based on YES/NO, create a table using the enter data option:

Add this measure:

Path Installs = 
COUNTROWS(
    FILTER(
        Q_Domain_Controllers_Latest ,
        [Critical_Patch_Installed] = SELECTEDVALUE('Path Installed'[Path Installed Status])
    )    
)

Show it in a column chart or any other suitable visual you prefer.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[PowerBeeEye]

thank you very much, @Fowmy  this looks like it works very well!

[PowerBeeEye]

@v-yiruan-msft Unfortunately, I cannot select a many to many relationship since I'm using the Power BI Desktop optimized for Reporting Server and I intend to save it on the reporting server.

My only options are *:1, 1:1 and 1:*

How can we workaround this?

[Fowmy · Accepted solution]

@PowerBeeEye 

Please check now:

You can download the file: HERE

Critical_Patch_Installed = 

VAR _UPDATE = 
SELECTCOLUMNS(
FILTER(
	GENERATE(
		'CVE-2020-1350',
		Q_Domain_Controllers_Hotfix_Latest
	),
	'CVE-2020-1350'[KBID] = Q_Domain_Controllers_Hotfix_Latest[HotFixID]
),
"_SERVER", Q_Domain_Controllers_Hotfix_Latest[ComputerDNS]
)

RETURN
IF( 
    SELECTEDVALUE(Q_Domain_Controllers_Latest[dnshostname]) IN _UPDATE,
    "Yes",
    "No"
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn