Forum Discussion

PowerBeeEye's avatar
PowerBeeEye
Microsoft Employee
6 years ago
Solved

Conditional column based on value from another table with matching values

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:

  1. For each row in the Servers table:
    1. If a match exists between Servers[Server_OS] and Critical_Patch[OS]
      1. If Servers[Server_Latest_Patch] equals Critical_Patch[KB_ID]
        1. Set Servers[Critical_Patch_Installed] to "Yes"
        2. Else set Servers[Critical_Patch_Installed] to "No"

 

What’s the best way to do so?

Need Help
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    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.

4 Replies

  • v-yingjl's avatar
    v-yingjl
    Community Support
    6 years ago

    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.

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

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

    • PowerBeeEye's avatar
      PowerBeeEye
      Microsoft Employee
      6 years ago

      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:

      1. Create a conditional column named Critical_Patch_Installed in the Servers table
      2. For each row in the Servers table:
        1. If a match exists between Servers[Server_OS] and Critical_Patch[OS]
          1. If Servers[Server_Latest_Patch] equals Critical_Patch[KB_ID]
            1. Set Servers[Critical_Patch_Installed] to "Yes"
            2. Else set Servers[Critical_Patch_Installed] to "No"

      Thanks!

      • richbenmintz's avatar
        richbenmintz
        Resident Rockstar
        6 years ago

        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!