Forum Discussion
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 y...
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.