Forum Discussion

PhilC's avatar
PhilC
Resolver I
3 years ago

Data model advice - data table with fields with multiple values

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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.

PhilC's avatar
PhilC
Resolver I
3 years ago

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