Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Matching Data in two tables

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. ...
  • v-lili6-msft's avatar
    v-lili6-msft
    8 years ago

    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's avatar
Anonymous
Not applicable
8 years ago

Hello Anonymous

 

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.

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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.