Comparing 2 excel sheets

[aishak12]

I am importing 2 excel sheets. I have one that lists all active employee's, the other one lists employee's with borrowed items. 

Is there a DAX function I can use to run (Active employee's against employee's with borrowed items) 

The result will be terminated employee's as anyone without a borrowed item is no longer working for this company.

[MauriceMecowe]

Hi @aishak12 ,

Have a look at this post, I think this is what you're looking for.

https://community.powerbi.com/t5/Desktop/Compare-2-columns-from-2-different-sheets/td-p/318659 

Cheers,

Maurice

[Greg_Deckler]

@aishak12 Yes, you can use EXCEPT for this purpose, so something like:

Table =
  VAR __ActiveEmployees = SELECTCOLUMNS('Sheet1',"Employee",[Employee])
  VAR __BorrowingEmployees = SELECTCOLUMNS('Sheet2',"Employee",[Employee])
RETURN
  EXCEPT(__ActiveEmployees,__BorrowingEmployees)

[edhans]

You could use the LOOKUPVALUE() function, which will allow you to look up values in one table against another. Then you could use the ISBLANK() function to see if you returned anything. I'd need data though to show you an example.

Ideally you could do this in Power Query with a merge. That is where I would try to do this as modeling there typically works better than in the DAX side, which is best for analysis.

But in any case, I'd need some data @aishak12 

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[aishak12]

Query 1 (Excel sheet 1) = EmployeesList 

Column name = Username

Query 2 (Excel Sheet 2) = redbeam

Column name = Username

Could you help me write a function that compares username for both sheets.

[v-lili6-msft]

hi  @aishak12 

Above solution should all work for your case,

Table =
  VAR __ActiveEmployees = SELECTCOLUMNS('EmployeesList','EmployeesList'[Employee])
  VAR __BorrowingEmployees = SELECTCOLUMNS('redbeam','redbeam',[Employee])
RETURN
  EXCEPT(__ActiveEmployees,__BorrowingEmployees)

https://docs.microsoft.com/en-us/dax/except-function-dax

IF not your case, What is your expected output?

Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,

Lin

[edhans]

What do you mean "compares" @aishak12 ?

• That the same thing in row 1 of the first sheet is the first thing of the second sheet, and so on?
• That the names in sheet 1 exist in sheet 2? (true/false indicator)
• that the names in sheet 2 exist in sheet 1? (true/false indicator)
• Do the comparisons need to be case sensistive?
• Do you want the result to be only names that are:
  • in both columns,
  • or only in sheet 1 and not sheet 2,
  • or only sheet 2 and not sheet 1,
  • or all names not in both sheets?

Be very clear what you are trying to accomplish. "compare" is very generic.

Can you provide actual data too, not screen shots. Don't make us key stuff in. 😁

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum