Forum Discussion

Apprentice's avatar
Apprentice
Copper Contributor
Nov 05, 2025

Building relationships in data model to leverage power pivot - circumventing unique key ID issue

When building relationships between two data tables in the data model of excel, you require a unique ID / key to relate the two datasets.   This makes sense but I find it has an issue:

It requires you to reduce the entirety of your dataset down to unique ID's in one of your tables so that it can do the 'one to many' relationship build.  If you don't have unique ID's / key, the relationship between two tables often doesn't work.   Yes you can use an intermediary table with unique ID's to link the two complete tables, but when using that intermediary table I find you are limited in what additional data rows you can bring into the combined power pivot table to provide context to the relationship.   Once you start bringing in contextual rows from the separate tables, there ends up being a lot of repeated values for any numerical figure from the other table, which you are trying to aggregate.    An example below of the power pivot table result (data from table 1 in blue, from table 2 in green)

 

Product ID (Unique Key)              Service Line                       Sales Amount                           COGS

169AKY                                        T Shirts                                 $1,000                                      $7,500

169AKY                                        Sweaters                               $2,000                                      $7,500

169AKY                                        Socks                                    $3,000                                      $7,500

169AKY                                        Turtle necks                          $4,000                                      $7,500

169AKY                                        Pants                                    $5,000                                       $7,500

 

COGS from table 2 also has the data broken out by Service Line (T-shirts, Sweaters, etc.), but will only show an aggregate COGS value for all service lines for each unique product ID, instead of breaking it out by Service Line.   This is because the Service Line column is being inserted into the power pivot table from table 1, it breaks out sales by Service Line.  If you insert the Service Line data column into the power pivot from table 2, COGS would be broken out by Service Line, and only an aggregate for sales would be produced.    Ideally the power pivot would display sales AND COGS broken out by Service Line (and Product ID).  

Is there a best practice for building relationships (unique ID's) between data tables:

  1. Without reducing the richness / variety of columns and data in your original tables (when creating the unique ID)?   Maybe 'group by' function in power query to obtain unique ID's before building the relationship?
  2. With retaining the ability to slice and dice the data by different characteristics (assuming they are present in both source tables) and have that detail presented in the power pivot.

 

Thanks for the help!

The humble Apprentice

BI & Data Analysis
community
excel
power bi

1 Reply

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Nov 06, 2025

    In general it's not necessary to build bridge table to handle many-to-many relationships, as variant you may use virtual relationships. based on your sample let assume we have two tables

    For them we to calculate COGS we may use measure

    COGSv:=CALCULATE( SUM(COGS[COGS] ),
     TREATAS(
           VALUES( Sales[Service Line] ),
           COGS[Service Line]
      )
)

    which gives

    Compare with

    Total COGS:=SUM( COGS[COGS] )

    More details at Physical and Virtual Relationships in DAX - SQLBI and you may google for other sources.

Resources