Best Practice for using same lookup tables across multiple fact tables

[zz99b0]

I need some guidance for best practices with lookup tables.  I’m learning Power BI and have done the best I can to search for examples, but I have not found a similar issue that I could translate to my situation.  I’m not married to my data model and have flexibility to make changes to my extracts, if needed.

Background:

I’m in the process of build Power BI Reports to provide insights into our labor planning/resource management data.

My model has HR data that captures our resources and attributes for employees, contractors, and planned hires.  This data has typical master data that is sourced from lookup tables, like Job Code, Location, Primary Skill (Capability).  This is referred to as SUPPLY data.

My model also has our labor plans related to the project work going out 5 yrs.  This represents the individual roles required to complete the project.  A subset of the role which identifies who is filling the role (effort and timing) and the gap that is not yet filled.  This data has references to the same lookup tables, like Job Code, Location, Skill … I have one table that captures the role and I have another table that captures the resource assignment and any gap associated to the role.  From an extract perspective, the detailed data is duplicated between the two tables. This is referred to as DEMAND data. 

In order to do labor planning, I have to be able to report our gap/surplus of supply based on the “ask” from the project teams.  This gap/surplus needs to be able to be shown based on a variety of attributes (aka the lookup tables).

I have relationships in my model that links SUPPLY data to the lookup tables and DEMAND data to the same lookup tables.   I also have relationship between SUPPLY data and DEMAND data to link together when a project role is filled by a resource.

I’m in struggling at how to best build the data/relationship model (or if necessary, to rebuild my underlying data to make this work).

An example of a couple key data views I need to be able to provide is:

• How much DEMAND do I have by skill?
• How much SUPPLY do I have by skill?
• What does the view look like in how we priced by job code and how it was staffed?

The current model has the relationship between DEMAND and the look-up tables as inactive.  While I understand how to use the DAX USERELATIONSHIP when creating measures, I do not understand how when I build a visual that is not using a measure, how do I get Power BI to use the relationship I need.

For example, when I build a visual to show distribution of skill on the DEMAND data, it doesn’t come back with any skills.

This is a snapshot of the relationship between one of the lookup tables and the SUPPLY data.  All of my lookup tables to SUPPLY  relationships look similar. When I create visuals on SUPPLY this works as expected.  I’m able to create filters and the visual provide the expected results.

This is the relationship view for DEMAND data.  The relationship from the lookup tables to DEMAND data is Inactive.  I do not know how to make the relationship active when I use a standard visual like a matrix view.

On this type of visual, I need it to pull the Capability from the right tables.  Right now the data is based on the Resources Primary Capability and no ability to see the combined data of SUPPLY and DEMAND.

I have read that one option is to duplicate the lookup tables.  I can certainly do that, but my follow-up question would be how do I make my Page Filters only show one lookup table for the user that would impact both tables.

One final complexity in my data model.  Is the relationship between SUPPLY and DEMAND has a relationship to Resource ID.  I needed to make that relationship as CROSS FILTER = Both to work as expected on some measures.

It is ok to say, WOW you really have the data model and data wrong.  I can rebuild my extracts for Power BI as needed if that makes the solution easier, as I built the extracts with limited Power BI knowledge.

Any guidance would be greatly appreciated.

Jennifer

[lbendlin]

Generally you will want to unlearn the term "lookup table".  In Power BI it has the meaning of a table that is not connected to the data model.   You will want to let the data model do as much work as possible (including the use of USERELATIONSHIP for secondary joins)  and only resort to disconnected tables if absolutely necessary.  Read up on TREATAS() vs LOOKUPVALUE() too.