Data model advice - data table with fields with multiple values

PhilC,

There are multiple ways to create this model. I prefer the approach below, since it avoids bidirectional relationships. As I understand it, the relationship between publications and authors is many-to-many (a publication can have multiple authors, and an author can have multiple publications). The concept is to create an Author Group, and use Author Group Key in your fact table. The table DistinctAuthorGroup is a bridge table that can be created in Power Query or DAX. This pattern can be applied to Organisation and Field of Research.

Model:

Data:

Calculated table:

DistinctAuthorGroup = DISTINCT ( AuthorGroup[Author Group Key] )

Measure:

Total Sales = 
CALCULATE (
    SUM ( FactTable[Sales] ),
    CROSSFILTER ( AuthorGroup[Author Group Key], DistinctAuthorGroup[Author Group Key], BOTH )
)

The measure uses CROSSFILTER to enable a bidirectional relationship in the context of the measure.

Result:

Note that the Author 1 total equals the grand total. This is due to the fact that Author 1 is involved in each publication.