Data model advice - data table with fields with multiple values

[PhilC]

Am after advice about setting up a data model where there are multiple fields that could have multiple values.

Situation is a data (fact) table for publications.  Each publication has a number of attributes which are in the columns.  There are a number of attributes that can have multiple field for a give publication.

Examples

1. Author – for each publication there can be multiple authors.  There are a number of fields that relate to the author, not just one field  (ie Name, Surname, Country, Type)
2. Organisation – multiple organisations can be attached to a publication.  Single field for this one (Discipline Code)
3. Field of Research – there can be multiple FORs for a publication.  There are two fields here, FOR Code and Percentage

The author / organisation / FOR fields are not related to each other.

We have been operating with the fact table at the publication-author level, but now need to report on these other elements.

How do I best model the fact table to accommodate these different grains for some fields existing in the data?  The solution needs to be flexible and I expect there to be more fields where there are multiple values going forward (ie hoping not to replicate data for each field needing to be modelled).

Have searched but did not locate a similar discussion, please send link if aware of one.

Thanks for your thoughts

[DataInsights]

@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.

[PhilC]

Thanks @DataInsights 

Am digesting this to see if I can implement it.

I am not understanding the AuthorGroup table.  I have a unique value for Author.  Where does the Author Group Key in teh Author Group table come from?

Thanks

[DataInsights]

@PhilC,

You would have to build the AuthorGroup table, preferably as part of an ETL process. Do you have access to an ETL developer or are you building this from scratch in Power BI?

[v-yiruan-msft]

Hi  @PhilC ,

If I understand correctly, it seems that what you want to get is to get a report which the data is from different tables. And these tables are not related. Please correct me if my understanding is wrong. Could you please provide some sample data of Author, Organisation, Field of Research and the fact table at the publication-author level? Also please share your expected result with backend scenario and the special example. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

In addition, please review the following links. Hope they can help you.

Data Modelling In Power BI: Helpful Tips & Best Practices 

 

How to display data from different data source tables in a single table in Power BI

How to Show Values from Two Tables on One Power BI Visualization

Best Regards

[PhilC]

Hi Rena, 

I do not think you are understanding the data.  The data is all related to one table - the publication.

I cannot really provide a model, because this is what I am asking for assistance on.

For example.

Publication ID = 1

- this publication has mutiple authors (Peter, Paul, Mary)

- this publication has FOR codes (123 - 50%, 456 - 50%)

- this publication has Departments (Dept A, Dept B, Dept C)

There is no direct relationship between Author and FOR and Department, so having them on the same rows is not necessarily correct.

I want to set up the model to have Departments and FORs as lookup tables and be able to filter and slice on them.

I would generally be doing a DISTINCTCOUNT of Pub ID.

There are many other fields (per Pub ID) that I use for different measures too.

Hope that helps fill out the picture a bit.  

Thanks