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

Anonymous
Not applicable

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

 

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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors