Related table best practices

[kman42]

I could use some advice on best practices with related tables. I recently finished Rob Collie and Avichal Singh's book "Power Pivot and Power BI" and am having trouble applying some of their concepts to my data. Specifically, I'm having trouble wih their suggestions around how to think about Data and Lookup tables and relationships. Their ideas seem best suited to situations where a single business process is represented by a single Data table, but I'm having difficulty applying that notion to my situation.

The simplified version of the underlying datasource that I am bringing in with ODBC includes four tables: Proposals, Team Members, Budget Detail, and Status History. The key field proposal_id ties them all together. Every Proposal record is associated with one or more Team Member records, at least one and up to five Budget Detail records (budget categories and amount), and virutally unlimited Status History records. As far as I'm concerned, all of these would be Data tables. 

Of course, I also have a People table that is associated with Team Members using the person_id key field and I created a Calendar table and related it to the Proposal date field. I understand both of these would be considered Lookup tables in their scheme and operate as filters flowing 'downstream'. 

Where I am struggling is with the four Data tables. I related them all through the proposal_id field, but when I start adding fields to a table visual from more than one of these, I get an error. I can add fields from the Proposal table and one other table, but once I add fields from the Proposal, Team Members and Budget Detail table, the error pops up. I think it is because each of those other tables has a 1-many relationship with the Proposal table. 

Should I be using Power Query to combine these tables first, essentially creating multiple Proposal records with the same proposal_id, but with rows for every combination of Team Member and Budget Detail? I think that would work, but it seems to go against Collie and Singh's axiom of never flattening.

Any help in how to think about this would be greatly appreciated.

[MattAllington]

Data table design can be complex.  You don't mention what the error you get says.  My guess is it is a memory error caused by the many to 1 relationship from your 3 data tables that join the one proposal table.  From your text based description, it sounds like the proposal table is a header table and the other 3 are data tables.  A header table can be a data table and also a lookup table.  

When you add columns to a visual into any location other than the Values section, you are implicity saying "I want to summarise based on this column of data".  But summarisation only flows from the 1 side to the many side efficiently.  ie, if you add a column from the proposal table and a column from the status history table into the rows section of a matrix, then say "total" the budget, you are effectivley creating a cross join between the tables - not what you want.

I don't know what reporting you are expecting from your data.  As long as you are putting columns into the rows/columns of visuals and then the values section contains measures that can find a filter path directly to those columns, you should be ok.

[kman42]

The error is "Can't determine relationship between fields." I think it is because I am trying to go 'uphill' from Team Member through Proposal to Budget Detail. I am trying to create a simple matrix as follows:

                          Budget Cat A     Budget Cat B    Budget Cat C     
Proposal 1
      Team Member A              $100            $200            $300
      Team Member B              $50             $100            $150

Proposal 2
      Team Member D              $25              $50             $40
      Team Member X              $60              $70             $20
      Team Member Z              $55              $20             $30

It's really just a simple report showing dollar amounts for each Team Member in each Budget Detail category grouped by Proposal. Ultimately, I'd like to group the Proposals by the last status update from Status History.

[Nathaniel_C]

Hi @kman42 ,

@KenPuls  who with Miguel Escobar wrote M is for (Data) Monkey and @MattAllington  who wrote Supercharge Power BI, are both on this forum.  I recommend both books highly. Maybe they will see this post and answer.  Also there are a lot of blogs out there and youtube videos. This is a good forum to ask this question as well.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel