Forum Discussion
LOOKUPVALUE not returning all values
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!!
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])
)
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
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
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!!
Instead of using two tables, you can use two columns and two relations. one with both and the other with one-way relation
