Modeling with multiple facts table

Hi XY_Z 

Here are some suggestions for your questions:

You might consider using a star model to merge the dimension tables for each level into a single Dimension table.

Then create a one-to-many relationship with the fact tables.

Alternatively, you can aggregate the fact tables of Level 1 and Level 2. The aggregation process reduces the table sizes in the semantic model, allowing you to focus on important data and helping to improve the query performance.

And you can set the storage mode to import, which can avoid the performance load caused by data queries.

Optimize DirectQuery models with table level storage - Training | Microsoft Learn

Introduction to performance optimization - Training | Microsoft Learn [76e3-6b80-ade-1019]

Best Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.