Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
maxbbbfff
Regular Visitor

Data Model

Hi,

 

I have a data model that in a simplified way has 5 tables:

 

>>> TAB1: the granularity of this table is the ID_OPE and there is a metric associated with this table (fact)

>>> TAB2: the granularity of this table is the ID_SUB and there is a metric associated with this table (fact). The same ID_OPE can be associated with more than one ID_SUB.

>>> TAB3: the granularity of this table is the ID_LIB and there is a metric associated with this table (fact). The same ID_SUB can be associated with more than one ID_LIB

>>> TAB4: the granularity of this table is the ID_OPE_EVENT. The same ID_OPE can have several ID_OPE_EVENT

>>> TAB5: the granularity of this table is the ID_SUB_EVENT. The same ID_SUB can have several ID_SUB_EVENT

 

I want to be able to relate information from all tables in the same view and filter bidirectionally as needed. However, I can only make the model as a whole work if I put a many-to-many relationship, as shown below:

 

maxbbbfff_0-1663168641061.png

 

 

Doing the above model looks like everything is working. However, I would like to know what the best practice would be (I don't think it's possible to use a star model and I can’t use a single association table, with all ids, as it results in tens of millions of rows in my model, impacting performance ).

 

I'd like to know if there are any issues I should be concerned about this model above, and if so, which other model should I use.

2 REPLIES 2
Anonymous
Not applicable

Hi @maxbbbfff ,

 

You may consider to remove table1 and create many to many relationship between table2 and table4.

 

Best Regards,

Jay

Hello, Thanks for the feedback. I need the existing information in the 5 tables. I summarized the tables by just putting the IDs, but they all have important data. In particular, tables 1, 2 and 3 are fact tables.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.