Multiple Active Relationships

[Dgreen23]

I have a master company table that I link everything to. Now I have run into an issue where I need a table to look at that master company table but also look at another table and link to its department section. If I link on the Company then it gives all results for the company but will not filter down when a department is selected. If I link on the department it filters down by department but shows all companies and the people associated to that department. Help.

[parry2k]

@Dgreen23 can you share sample data and relationship diagram and required result. It should work but need more details.

[Dgreen23]

@parry2k ,

I've attached a diagram. I cannot share the data, unfortunately. The required result is that if company x is selected and then their lathe department is selected then I should only see the company x lathe department. Sorry for the lack of data. Let me know if there is anything else I can provide

  

[v-kelly-msft · Accepted solution]

Hi  @Dgreen23 ,

Maybe it's a direction issue,see below:

Pic 1:Filter specification will flow from CompanyProject to Project Hours (as shown in the following image), but it won’t flow up to CompanyEmployee.

Pic 2:However, if you set the cross filtering direction to Both, it will work. The Both setting allows the filter specification to flow up to CompanyEmployee.

'

So try to modify the directions between tables,maybe you will get the right result.

Here is the reference.

https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[parry2k]

@Dgreen23 although changing the relationship direction to both will solve the problem but I will not use it as it can have a performance impact. Read more here.

The recommendation is to use CROSSFILTER function in the measure and use both  in it where required

Cheers,

P

[parry2k]

@Dgreen23 so you have a department table that also has a relationship with utilization by company table.

[Dgreen23]

@parry2k 

No sir. Both of the utilization by company tables hold identical data. However, the key difference between them is one is calculated strictly by the department and the other one is on an employee basis. Unfortunately, I have not been able to get the data to come together correctly. Hence why I need to combine those two further. 

[parry2k]

@Dgreen23 as much as I want to get this resolved but I still not fully sure. Why not put sample data in an excel (redact it) and send it over, it will help to understand the problem and hence the solution.