cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Arminx
Frequent Visitor

LOOKUPVALUE not returning all values

Hi All 

 

My model: 

 

 

schema.PNG

 

 

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!

 

 

 

 

1 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!!

View solution in original post

6 REPLIES 6
v-haibl-msft
Microsoft
Microsoft

@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.

LOOKUPVALUE not returning all values_1.jpg

 

Best Regards,

Herbert

scottsen
Memorable Member
Memorable Member

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
Frequent Visitor

Hey thanks @scottsen 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

 

@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.

LOOKUPVALUE not returning all values_1.jpgLOOKUPVALUE not returning all values_2.jpg

 

Best Regards,

Herbert

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

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!!

View solution in original post

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors