Power BI Data model

[Anonymous]

Hi All,

We have multiple fact tables in the model (In this pic , i have shown only 2 fact table) and 10 dimension tables.
All the Dimension & fact tables are SCD type 2 and we need to do point in time reporting. In one of the matrix visualization we need to show data from DIM1 & FACT2 and we do not want to enable Bi-directional as its not recommended. Initially our data model was having many to many relationship and to avoid that we created this factless fact for each fact table as the granularity of the fact tables are different. Please suggest an approach to avoid bidirectional & complex DAX queries on the report, as we are giving this report to the end-user for self service visualization.

Awaiting your response

@Patrik, @Guy ,

@amitchandak

[Anonymous]

Hi  @Anonymous ,

According to your case description and the data modeling diagram you have provided so far, your five-dimensional tables are connected to your two fact tables using one-to-many one-way relationships according to your needs, I think this is a more secure and feasible solution, in the perspective of data modeling has avoided many-to-many inter-table relationships, for complex DAX queries, my understanding is that your inter-table relationships do not cause complex DAX queries, but complex calculated columns that you create in existing models, Calculated tables will cause large resource consumption and load time when refreshing, specifically for report analysis, you can try the desktop performance analyzer tool to find out the DAX operations or visuals that take more time.

Use Performance Analyzer to examine report element performance in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,

Neeko Tang

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

[Anonymous]

My query is , I have a requirement to create a matrix visualization, by pulling the column from  DIM1 and Fact2. How can i achieve this without enabling Bidirectional and writing DAX expressions on the report?

However, I need to build the datamodel and give it to my users to create reports for self service visualization. Users wont be able to write DAX queries.

Awaiting for your help.