Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have two tables:
Servers
Server_Name | Server_OS |
| Server_Latest_Patch | |
Server_1 |
| Windows Server 2016 |
| KB123 |
Server_2 |
| Windows Server 2019 |
| KB789 |
Server_3 |
| Windows Server 2016 |
| KB456 |
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 |
| Server_Latest_Patch |
| Critical_Patch_Installed |
Server_1 |
| Windows Server 2016 |
| KB123 |
| No |
Server_2 |
| Windows Server 2019 |
| KB789 |
| Yes |
Server_3 |
| Windows Server 2016 |
| KB456 |
| Yes |
The logic being:
What’s the best way to do so?
Solved! Go to Solution.
Hi @PowerBeeEye ,
You can create a calculated column using the below dax formula:
Critical_Patch_Installed =
IF (
'Servers'[Server_OS] IN DISTINCT ( 'Critical_Patch'[OS] ),
IF (
'Servers'[Server_Latest_Patch] IN DISTINCT ( 'Critical_Patch'[KD_ID] ),
"Yes",
"No"
),
"No"
)
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi @PowerBeeEye ,
You can create a calculated column using the below dax formula:
Critical_Patch_Installed =
IF (
'Servers'[Server_OS] IN DISTINCT ( 'Critical_Patch'[OS] ),
IF (
'Servers'[Server_Latest_Patch] IN DISTINCT ( 'Critical_Patch'[KD_ID] ),
"Yes",
"No"
),
"No"
)
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
@PowerBeeEye , Try new column in Servers table
if(isblank(countx(filter(Critical_Patch,Servers[Server_OS] =Critical_Patch[OS]),Critical_Patch[KB_ID])),"No","Yes")
when Critical_Patch to Servers is 1 to M realtion
if(isblank(related(Critical_Patch[KB_ID])),"No","Yes")
thank you, @amitchandak , but I don't see where the matching of the Critical_Patch[OS] and Servers[Server_OS] columns are being done.
To state again, this is what I am trying to do:
Thanks!
Hi @PowerBeeEye,
You can do this in Power Query by Merging your Server table and your critcal patch tables on the join keys between the two tables and a custom column M Code Below
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk4tKkstijdU0lE6tEABjIDM8My8lPzyYgWIrIKRgaEZqgJvJ0MjY6VYHbgBRoQMsEQ3wNzCEtkAY5JdYGJqphQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Server_Name = _t, #"(blank)" = _t, Server_OS = _t, #"(blank).1" = _t, Server_Latest_Patch = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Server_Name", type text}, {"(blank)", type text}, {"Server_OS", type text}, {"(blank).1", type text}, {"Server_Latest_Patch", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"(blank).1", "(blank)"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Server_OS", "Server_Latest_Patch"}, Critical_Patch, {"OS", "KB_ID"}, "Critical_Patch", JoinKind.LeftOuter),
#"Expanded Critical_Patch" = Table.ExpandTableColumn(#"Merged Queries", "Critical_Patch", {"KB_ID"}, {"KB_ID"}),
#"Added Custom" = Table.AddColumn(#"Expanded Critical_Patch", "Critical Patch Installed", each if [KB_ID] <> null then "Yes" else "No")
in
#"Added Custom"
you can also create a custom column in your Servers Table using DAX
Patched =
var patched = LOOKUPVALUE(Critical_Patch[KB_ID], Critical_Patch[KB_ID], 'Servers'[Server_Latest_Patch], Critical_Patch[OS], [Server_OS])
return if(patched <> BLANK(), "Yes", "No")
Hope this helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |