Multiple slicers with multiple cross filters

[MikeL]

Hi all,

I have 2 different SQL querries that I'm looking to use the same filters for.  JobCosts_Billings contains cost data, while LaborHours contains information on who worked on a job during a specific time. 

Originally I started with the JobCosts_Billings data and had slicers that would filter based on the previous slicer selections.  The slicers are in the middle of my Manage Relationships image, but they were the fields in the JobCosts_Billings query, so as I selected a DivName, it would only show me the ProjMan that was associated with that division and so on. 

I created the tables in the middle to create distinct values so that I can filter both JobCosts_Billings and LaborHours at the same time. This works fine, but now I don't have relationships between the different levels of heirarchy, DivName --> ProjMan --> BusinessUnit --> WorkOrders.  

Any suggestions on how I can make filtering of both tables while still maintaining a hierarchical slicer structure work with what would essentially be multiple cross filters (I think, new to PowerBI)?  I'm thinking duplicating JobCosts_Billings (or likely create a distinct DivName, ProjMan, BusinessUnit, WorkOrders for performance purposes) and relating things through that may work but I'm not sure where/how to associate these items.

Thanks in advance.

[MattAllington · Accepted solution]

If it is a true hierarchy, then you need to create a single header table that contains all the work orders as the primary key. Every work order appears once and only once. Include the other 3 columns in the hierarchy in this table too. Then join this table to the other 2 tables via the work order number

[MikeL]

Also, I was reading some of your other answers on similar problems, definitely appreciate your contributions.  

[MikeL]

Thanks Matt, I was actually approaching something similar at first but couldn't get it to work.  Just for clarification, by doing this would I be able to filter by Division and it would change the values in each of my visualizations?  Also, not every BusinessUnit has a WorkOrder, so there would be many null workorders associated with an always unique BusinessUnit.  If a BusinessUnit does have a WorkOrder the work order is unique.  I'm thinking what you suggested will work, but I'd need an extra step to have it link at the BusinessUnit level if WorkOrder is null.  

[MattAllington]

---

@MikeL wrote:

Not every BusinessUnit has a WorkOrder

---

It sounds like you should exclude workorder from this lookup table and instead use Business unit as the unique key

you can’t have null values as the unique key column by definition.