LOOKUPVALUE - A table of multiple values was supplied..

[tjlundquist1]

I use the LOOKUP formula in conjunction with the PATH formula to create a Hierarchy for my RLS. Almost two years solid with no issues, but today - I ran accross this error and am having trouble correcting it the right way. 

Currently using:

Org Level 1 = LOOKUPVALUE('Active Directory'[Employee Email],'Active Directory'[Employee Name],PATHITEM('Active Directory'[Hierarchy],4,TEXT))

I attempted a fix in which I learned the potential issue. In the power query, I filtered out NULLS in the name column. This corrected the error, but completely threw off my Org Level columns. For ex. in the first row I would expect that Org Level 2 would be Kara, however after I filtered out the NULLS it was not Kara it was just blank. Kara was in Org Level 4, which doesn't make any sense.

So the issue potentially has something to do with NULLS in [Employee Name] data? 

Any ideas?

Trevor

[amitchandak · Accepted solution]

@tjlundquist1 , Try a new column like

Org Level 1 =
var _1 = PATHITEM('Active Directory'[Hierarchy],4,TEXT)
return
maxx(filter('Active Directory' ,'Active Directory'[Employee Name] =_1), 'Active Directory'[Employee Email])