Forum Discussion
Help with comparing each row to a column in a different table
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 the computers are up to date or not. Here are how my tables look like:
Table 1:
| APPLICATION 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:
| APPLICATION NAME | 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:
| APPLICATION NAME | VERSION | COMPUTERNAME | COMPLIANCY 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 trouble coming up with the dax formula or code for this as I am quite new to powerBI. Any help would be appreciated! Thanks so much!
allenind
You can add the following as a new column in Table 1. My Table 1 and 2 are T-1 and T-2, replace accordingly.COMPLIANCY CHECK = IF( ISEMPTY( FILTER( 'T-2', 'T-2'[APPLICATION NAME] = 'T-1'[APPLICATION NAME] && 'T-2'[VERSION] = 'T-1'[VERSION] ) ), "NOTCOMPLANT", "COMPLANT" )________________________
If my answer was helpful, please click Accept it as the solution to help other members find it useful
Click on the Thumbs-Up icon if you like this reply 🙂
1 Reply
- Fowmy
Super User
allenind
You can add the following as a new column in Table 1. My Table 1 and 2 are T-1 and T-2, replace accordingly.COMPLIANCY CHECK = IF( ISEMPTY( FILTER( 'T-2', 'T-2'[APPLICATION NAME] = 'T-1'[APPLICATION NAME] && 'T-2'[VERSION] = 'T-1'[VERSION] ) ), "NOTCOMPLANT", "COMPLANT" )________________________
If my answer was helpful, please click Accept it as the solution to help other members find it useful
Click on the Thumbs-Up icon if you like this reply 🙂