0

I am trying to create a calculated column that compares one column to another column in a different table, I want to compare the version of the applications installed in different computers so I can monitor if I am up to date or not. Here are how my tables look like:

Table 1

App name Version ComputerName
Adobe Acrobat Reader DC 19.010.20069 COMPUTER1
Google Chrome 87.0.4280.141 COMPUTER1
Microsoft Edge 87.0.664.75 COMPUTER1
SnagIt 12.4.0 COMPUTER1
Adobe Acrobat Reader DC 18.010.20069 COMPUTER2
Google Chrome 85.0.4280.141 COMPUTER2
Microsoft Edge 84.0.664.75 COMPUTER2
SnagIt 11.4.0 COMPUTER2

Table 2

App name Required Version
Adobe Acrobat Reader DC 19.010.20069
Google Chrome 87.0.4280.141
Microsoft Edge 87.0.664.75
SnagIt 12.4.0
Microsoft Visual Studio 10.0
Python Launcher 3.82

Expected Outcome

App name Version ComputerName Compliant Check
Adobe Acrobat Reader DC 19.010.20069 COMPUTER1 COMPLIANT
Google Chrome 87.0.4280.141 COMPUTER1 COMPLIANT
Microsoft Edge 87.0.664.75 COMPUTER1 COMPLIANT
SnagIt 12.4.0 COMPUTER1 COMPLIANT
Adobe Acrobat Reader DC 18.010.20069 COMPUTER2 NOT COMPLIANT
Google Chrome 85.0.4280.141 COMPUTER2 NOT COMPLIANT
Microsoft Edge 84.0.664.75 COMPUTER2 NOT COMPLIANT
SnagIt 11.4.0 COMPUTER2 NOT COMPLIANT

I have tried creating the relationship but despite that I can't seem to compare the two tables.

Allen Andre Indefenzo
  • 311
  • 5
  • 19

1 Answers1

2

First create an ID column in both tables. I combined the app name and the version to create an unique ID.

ID = COMBINEVALUES( "-", Table1[App name], Table1[Version] )

Then create a relationship between the tables using the ID columns.

Finally use the following DAX logic to create a calculated column in Table1

Compliant Check = 
IF( 
    NOT( ISBLANK( RELATED( Table2[ID] ) ) ), 
    "COMPLIANT", 
    "NOT COMPLIANT" 
)

This is the expected result:

enter image description here

Agustin Palacios
  • 1,111
  • 6
  • 10
  • You can even create an ID column using the index column in the query editor which can save some time. :) – Gowtham_7 Jan 14 '21 at 13:33