What's the Best structure for my Relationships between Document Header, Line, Line Category tables?

[elliottcarver]

Hello,

I'm wondering if this community can give some guidance on how to structure my relationships and measures.

I have the following tables that I'm trying to report on. The Document Line tables contain data like the [Quantity] fact-numbers I'm interested in reporting on.

Tables

Project	Document Header	Document Lines	Line Category
Project	Project Document Header Type A	Project Document Line Type A	Project Document Line Category (not a 'Dimension' table, each project can have different line-types)
	Project Document Header Type B	Project Document Line Type B	Project Document Line Category (same table as above, the lines on different project documents are the same for all project documents)

Data

See Excel for example data https://stradenergy-my.sharepoint.com/:x:/p/ecarver/EfI4O2o1L15Jlc94FzUDQFwB6BGLd-rveLxBpC6wzoNdLg?e... (is there a way to just upload my Excel file here? I think I've seen that on other posts)

Measures

Type A Total Quantity= Sum ('Project Document Line Type A'[Quantity])

Type B Total Quantity= Sum ('Project Document Line Type B'[Quantity])

Visualizations

I need to use that measure on these hierarchies:

Project

By Project Document Line Category Description

Project

By Project Document Type A

By Project Document Type B

Project Document Type A

By Project Document Line Category

Project Document Type B

By Project Document Line Category

So my question is: how do I structure my relationships?

Option 1

Does having Project Document Line Category connected to both Line tables create a problem?

• Project
  • Project Document Header Type A
    • Project Document Line Type A
      • Project Document Line Category

• Project
  • Project Document Header Type B
    • Project Document Line Type B
      • Project Document Line Category

Option 2

Does having a many-to-many relationship between Project Document Header and Project Document Line Category create a problem?

This seems more logical to but I'm not confident of the implications of many-to-many relationships.

• Project
  • Project Document Header Type A
    • Project Document Line Category
      • Project Document Line Type A

• Project
  • Project Document Header Type B
    • Project Document Line Category
      • Project Document Line Type B

[mwegener]

Hi @elliottcarver 

did you solve your problem?

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[mwegener]

Hi @elliottcarver ,

my recommendation would be as follows.

Create one "Document" fact-table with Power Query.

Take the document line as a base and extend it to the other information (document header, category)

PBIX

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.