topic Re: LOOKUPVALUE - Need Help in DAX Commands and Tips

LOOKUPVALUE - Need Help

yosemite — Wed, 29 Apr 2020 15:17:10 GMT

Hello,

I need to perform a look up of Table1[Column1] in Table 2[Column1] and return values that do not exist in Table1[Column1].

Appreciate any help.

Thank you.

Re: LOOKUPVALUE - Need Help

nandukrishnavs — Wed, 29 Apr 2020 15:34:18 GMT

yosemite

I would recommend you to use the merge option (right outer) in Edit Query instead of DAX.

https://docs.microsoft.com/en-us/power-bi/desktop-shape-and-combine-data#combine-queries

https://radacad.com/choose-the-right-merge-join-type-in-power-bi

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

Re: LOOKUPVALUE - Need Help

yosemite — Thu, 30 Apr 2020 15:19:00 GMT

Hello nandukrishnavs - Thanks so much! I was able to obtain a list of what doesn't exist in Table1 by using Right Anti.

What I'm trying to do is to get a list of Employee Name that does not exist in Table1 by 'Week'. Table2 contains distinct employee and does not have a 'Week' column.

TABLE1

Week	Employee Name
Week 1	Employee1
Week 1	Employee2
Week 1	Employee3
Week 1	Employee4
Week 1	Employee5
Week 2	Employee 1
Week 2	Employee 3
Week 2	Employee 5
Week 3	Employee 1

Table 2

Employee Name

Employee1

Employee2

Employee3

Employee4

Employee5

Any help is appreciated!

Re: LOOKUPVALUE - Need Help

camargos88 — Thu, 30 Apr 2020 15:54:04 GMT

Hi yosemite ,

Try this one:

Did I answer your question? Mark my post as a solution!
Ricardo

Re: LOOKUPVALUE - Need Help

yosemite — Thu, 30 Apr 2020 16:04:53 GMT

This didn't work. Thanks for trying.

Since my goal is to get a list of employees that do not exist in Table1 by Week, I would like my merged table to return the following:

Week	Employee Name
Week 2	Employee2
Week 2	Employee4
Week 3	Employee2
Week 3	Employee3
Week 3	Employee4
Week 3	Employee5

Re: LOOKUPVALUE - Need Help

nandukrishnavs — Thu, 30 Apr 2020 16:52:32 GMT

yosemite

Another alternate approach.

Table1

Week	Employee Name
Week 1	Employee1
Week 1	Employee2
Week 1	Employee3
Week 1	Employee4
Week 1	Employee5
Week 2	Employee1
Week 2	Employee3
Week 2	Employee5
Week 3	Employee1

Table2

Employee Name

Employee1

Employee2

Employee3

Employee4

Employee5

Present = var _count=CALCULATE(COUNT(Table1[Employee Name])) var _result=IF(ISBLANK(_count),0,_count) return _result

Create a matrix visual.

Now you can export this visual. You will get the below table.

Now you can filter zero.

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

Re: LOOKUPVALUE - Need Help

camargos88 — Thu, 30 Apr 2020 16:51:56 GMT

Hi yosemite ,

Take a look at this file: Download PBIX

Basically I grouped the values per week and joined with a left anti and filtered nulls.

Did I answer your question? Mark my post as a solution!
Ricardo

Re: LOOKUPVALUE - Need Help

yosemite — Thu, 30 Apr 2020 17:32:04 GMT

camargos88 - Thank you so much. I feel like I'm getting closer.

Could you walk me through how you 'Added Custom' (Step 6) and joined with a left anti?

Re: LOOKUPVALUE - Need Help

camargos88 — Thu, 30 Apr 2020 17:40:47 GMT

yosemite ,

First you group all your rows by week and created a new column with a grouped table column.

Table.Join(
#"Table (2)", "Employee Name",
Table.RenameColumns([Rows],
{"Employee Name", "EmployeeName"}),
"EmployeeName", JoinKind.LeftAnti)

Did I answer your question? Mark my post as a solution!
Ricardo

Re: LOOKUPVALUE - Need Help

yosemite — Thu, 30 Apr 2020 17:52:25 GMT

camargos88 I got to this part. See, my table1 and table2 contains many columns. For simplicity, I only posted 1 and 2 columns for the table.

What I'm trying to understand right now is do I list all the column names in table1 and table 2? See highlighted text in red. Also, where did you get 'EmployeeName' when the column names in both tables in PBIX are 'Employee Name'.

Table1.Join(
#"Table2", "Employee Name", Can I include more columns from Table 2?
Table1.RenameColumns([Rows],
{"Employee Name", "EmployeeName"}), Can I include more columns from Table 1?
"EmployeeName", JoinKind.LeftAnti)

Re: LOOKUPVALUE - Need Help

camargos88 — Thu, 30 Apr 2020 17:56:47 GMT

yosemite ,

Doesn't matter how many columns you have. This code just join both tables using the key column "Employee Name".

The "EmployeeName" is just a renamed column to join it.

Ricardo

Re: LOOKUPVALUE - Need Help

yosemite — Thu, 30 Apr 2020 18:09:20 GMT

camargos88

I'm getting an error Expression.Error: The name 'RollCallList_Test.RenameColumns' wasn't recognized. Make sure it's spelled correctly.

= Table.AddColumn(#"Grouped Rows", "Custom", each Table.Join(
#"ABCEmployees", "EMPLOYEE_ID",
RollCallList_Test.RenameColumns([Rows],
{"EmployeeNo", "EmployeeName"}),
"EmployeeName", JoinKind.LeftAnti))

Re: LOOKUPVALUE - Need Help

camargos88 — Thu, 30 Apr 2020 18:19:54 GMT

yosemite ,

Table.RenameColumns is a function, you can change it. You just change the parameters of it.

= Table.AddColumn(#"Grouped Rows", "Custom", each Table.Join(
#"ABCEmployees", "EMPLOYEE_ID",
Table.RenameColumns([Rows],
{"EmployeeNo", "EmployeeName"}),
"EmployeeName", JoinKind.LeftAnti))

Re: LOOKUPVALUE - Need Help

yosemite — Thu, 30 Apr 2020 18:41:53 GMT

camargos88 You are awesome! Thanks for your help.

Re: LOOKUPVALUE - Need Help

yosemite — Fri, 01 May 2020 13:57:04 GMT

Hello camargos88 - Just checking if you have an idea why I am not getting any 'null' EmployeeNo after using JoinKind.LeftAnti so I'm unable to filter it out.

I'm going back to your steps and want to make sure I didn't miss anything. This is what I did to group the week:

Highlight Week column
Transform > Group By
Basic > New column name: Type Rows > Operation: All Rows

Then I successfully added the Custom column with the code below.

= Table.AddColumn(#"Grouped Rows", "Custom", each Table.Join(
#"ABCEmployees (2)", "EmployeeNo", Table.RenameColumns([Rows], {"EmployeeNo", "EmpName"}),
"EmpName", JoinKind.LeftAnti))

The only problem is when I removed Rows column and expanded Custom column, I didn't find any null EmployeeNo.

Re: LOOKUPVALUE - Need Help

camargos88 — Fri, 01 May 2020 13:59:45 GMT

yosemite ,

You should get a return with 3 columns:

1 week column

2 employee num (1 from left side / 1 from right side).

If you don't have any nulls it's likely you don't have any employee for this week...it's sounds weird

Can you provide some samples of this week ?

Ricardo

Re: LOOKUPVALUE - Need Help

yosemite — Fri, 01 May 2020 14:46:30 GMT

camargos88 Although I didn't have to filter out 'null' values, it worked on this simple dataset; see link below.

My dataset has thousands of employees, and they are not being filtered out. I followed all the steps. 😣

Yosemite_v1.pbix

Re: LOOKUPVALUE - Need Help

camargos88 — Fri, 01 May 2020 15:03:18 GMT

yosemite ,

Check this new file: Download PBIX

Take a look at the week_missing table on Power Query.

Did I answer your question? Mark my post as a solution!
Ricardo

Re: LOOKUPVALUE - Need Help

yosemite — Fri, 01 May 2020 17:19:35 GMT

Thank you very much! camargos88

You've been extremely helpful.