LOOKUPVALUE not returning all values

[Arminx]

Hi All 

My model: 

I'm trying to find and store the name of the manager (corresponding to manager_id in employment table) in the employment table. I have added a column bossName. 

I've tried both: 

bossName = LOOKUPVALUE('public employee'[FullName],'public employee'[id],'public employment'[manager_id])

and 

bossName = CALCULATE(
	VALUES ( 'public employee'[FullName]),
	FILTER (
		 'public employee',
		 'public employee'[id] = 'public employment'[manager_id]
	)
)

but it works partially: only for two of manager_ids, it gets and populates results and for the rest it's empty/null. 

I've checked and there's corresponding rows for dozens of manager_ids. 

Something to do with relationship or am i missign something in the formula?

Thanks!

[v-haibl-msft]

@Arminx

If the data type and data value are the same in columns of 'public employee'[id] and 'public employment'[manager_id], corresponding FullName should be returned. Only if there is no match that satisfies all the search values, a BLANK is returned.

I created two simple tables with/without relationship between them, both of the formulas work well. So please check the data type and data value, and also check the relationship as scottsen said.

You can try to modify the active relationship to single direction as below.

Best Regards,

Herbert

[Anonymous]

Both of those formulas look correct to me.  I'm a bit surprised it is not working.   I only have two theories:

* The only *think* the id's match.  There is secretly leading/trailing spaces or something.

* The relationships are giving you weirdness.  I assume you the upper enabled relationship is employment[employee_id] to to employee[id]... so, by default its going to try and reduce your employee rows to just "the 1" side of the one to many relationship.  This is maybe made worse by the bi-directional filter (the relationship has 2 arrows).  I hate that feature.   To figure out if this is what's going on, just nuke both relationships 🙂

Assuming that inactive relationship is between employment[manager_id] and employee[id], I would try...

bossName = CALCULATE(
	RELATED ('public employee'[FullName]),
	USERELATIONSHIP ('public employment'[manager_id], 'public employee'[id])
)

[Arminx]

Hey thanks @Anonymous and @v-haibl-msft for your replies. 

The problem indeed seems to be with the two-way relationship. If i make it one-way it works as expected. 

But as troublesome as it is, I need propagation in both directions. There are for example cases that I filter employees and show breakdown by function, or select a boss and see how many hours pople under him have spent (via another relation between hours and employee). 

PowerBI doesn't seem to accept your suggestion:

bossName = CALCULATE(
	RELATED ('public employee'[FullName]),
	USERELATIONSHIP ('public employment'[manager_id], 'public employee'[id])
)

It says: 

"The column 'public employee[FullName]' either doesn't exist or doesn't have a relationship to any table available in the current context" .. doesn't make sense to me.

Even if I use ALL  to remove any filters, it still returns blanks. 

bossName = CALCULATE(
	VALUES ( 'public employee'[FullName]),
	FILTER (
		 ALL('public employee'),
		 'public employee'[id] = 'public employment'[manager_id]
	)
)

I also tried importing a duplicate table for "public employment" and hack relationship but couldn't get that working..

Should i start thinking about merging tables? Any ideas are welcome

[v-haibl-msft]

@Arminx

Maybe you can try to create a duplicate "public employee" table. And you don’t need to create any relationship for this table as below.

Best Regards,

Herbert

[Credential]

Instead of using two tables, you can use two columns and two relations. one with both and the other with one-way relation

[Arminx · Accepted solution]

Thanks @v-haibl-msft that could work, but i ended up merging queries. 

I'll keep your solution in mind and try it if i come across a similar situation. Thank you!!