Matching Data in two tables

[datavis]

In Power BI desktop I have two sets of data, both of which have a column called Employee ID. I need to create a column called Compliance using the Employee IDs that match between the two data sets.

If Training Compliance Employee ID matches Active Positions Employee ID then I need it to return Non-Compliant in my new Compliance column.

Hope that makes sense. Any help will be appreciated.

[v-lili6-msft · Accepted solution]

Hi@ datavis

You can use ISBLANK() function like below:

Column = IF(ISBLANK(LOOKUPVALUE(Table7[EmplID],Table7[EmplID],Table6[EmplID])) ,  "Compliant",
    "Non-Compliant"
)

Result:

Basic data

Best Regards,

Lin

[Anonymous]

I want to compare two columns in two different tables by text. After that I want to count them.

1. g Equipment Table Having Status Column with value “Not Submitted” & Same Values with Same Column Name in MASTER LIST Table. So I want total “Not Submiited” from Both table. Attached alos is PBX file
2.  

3. O&M	Not Submitted
   O&M	Not Submitted
   O&M	Not Submitted
   O&M	Not Submitted
   O&M	Not Submitted
   O&M	Not Submitted
   O&M	Not Submitted
   O&M	Not Submitted
   O&M	Not Submitted
   O&M	Not Submitted
   O&M	Not Submitted

4. DELIVERABLES	Status	TESTING WIR
   Mechanical Equipment	Not Submitted	NS
   Mechanical Equipment	Not Submitted	NS
   Mechanical Equipment	Not Submitted	NS
   Mechanical Equipment	Not Submitted	NS
   Mechanical Equipment	Not Submitted	NS
   Mechanical Equipment	Not Submitted	NS
   Mechanical Equipment	Not Submitted	NS
   Mechanical Equipment	Not Submitted	NS
   Mechanical Equipment	Not Submitted	NS
   Mechanical Equipment	Not Submitted	NS
   Mechanical Equipment	Not Submitted	NS
   Mechanical Equipment	Not Submitted	NS
   Mechanical Equipment	Not Submitted	NS
   Mechanical Equipment	Not Submitted	NS
   Mechanical Equipment	Not Submitted	NS
   Mechanical Equipment	Not Submitted	NS

[datavis]

Sorry, when I say Vacant, I don't mean Blank. The word Vacant is in the Name column when the position is vacant so ISBLANK won't work.

Column = IF(ISBLANK(LOOKUPVALUE(Table7[EmplID],Table7[EmplID],Table6[EmplID])) ,  "Compliant",
    "Non-Compliant"
)

So, if EmplID column in the delinquent table matches the the EmplID column in the Active Positions table then Non-Compliant but if Name is Vacant then Vacant.

[datavis]

That works! Thank you!

[Anonymous]

Hello @datavis

You can do this using the Edit queries or DAX, but you need to do a workaround, unfortunately you can't do it directly.

1. Using Edit queries theres an option called Merge Queries, this option has the same behavior of "lookupv", what you need to do using this option and bring any column no matter the content, for those where there was coincidence it will bring a value and for the others the value will be "null", after this you need to create a "Conditional column", and with this option you select the column that you bring with the Merge option and ask if the value is "null". Once you are in this option you will become familiar with what you have to do.
2. Using DAX is basically the same thing but you need to do it on a "Calculate column", using any of the functions "RELATED" or "LOOKUPVALUE"

I hope this will help you.

Good luck.

[datavis]

Thank you HarrisonCbe,

I used lookupvalue to get compliant or non-compliant.

Training Compliance =
IF (
    LOOKUPVALUE ('HHS-DSHS-ActivePositions'[EmplID],'HHS-DSHS-ActivePositions'[EmplID],'DelinquentTraining'[Emplid])
        ,
    "Non-Compliant",
    "Compliant"
)

If any names in 'HHS-DSHS-ActivePositions'[Name] is vacant, then 'Delinquent Training'[Training Compliance} column will show Compliant.

Not sure how to write that. Help appreciated.