Forum Discussion
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.
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.
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
6 Replies
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.
- 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.
- 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.
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.
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
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.
I want to compare two columns in two different tables by text. After that I want to count them.
- 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
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 DELIVERABLES Status TESTING
WIRMechanical 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