Regarding data model

[sree1610]

Hi,  
1) my fact table contains around 6000 rows data. and having 5 to 6 dimensional tables each having around 2000 rows of data.
need to use fields from all tables in single page of my power bi report.
so shall i merge each dimensional table with fact table as full outer join and create one to many relationship between fact and each of the merged table with bi directional filtering enabled.
i know that enabling bi directional filter will create ambiguity and slows the performance. but i am not finding any efficient way for this . 
Requirement is all thr visual should be interactive , so i think all tables should either be merged or connected via relationship.

2) need to show  some high level numbers(% Completion and #Apps in Incomplete state) at the top.
and entire dashboard to be interactive, if users click on anything on the dashboard including that number visual at top, entire dashboard should be interactive.
since card visual will not cross filter any other visuals, i used table visual as a workaround.
so anybody having suggestion instead of table visual, can i use any other visual for showing %completion, #apps in incomplete state.

thanks in advance

[pankajnamekar25 · Accepted solution]

Hello @sree1610 

Keep Star Schema: Don’t merge tables. Keep Fact and Dimension tables separate.
Relationships: Create one-to-many (Dim → Fact) with single-direction filters.
Avoid Bi-Directional Filters: They cause ambiguity and slow performance.

Interactivity
Use Table visual (styled like a card) for clickable KPIs.
Or use buttons + bookmarks if you want card-style visuals to trigger filters.
Result: Clean model, fast performance, and fully interactive Report

If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.


Thanks,


Connect with me on:

LinkedIn

[v-pgoloju]

Hi @sree1610,

Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[v-pgoloju]

Hi @sree1610,

Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to @KarinSzilagyi and @pankajnamekar25  for prompt and helpful responses.

Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[KarinSzilagyi · Accepted solution]

Hi @sree1610, please do not merge all your tables or connect all tables with bi-directional relationships, unless you want to create Frankenstein's data model from hell or a creative version of random number generator report.

Could you list your fact table(s) and dimensions with the keys connecting them and their current relationships? Even better if you could provide an example with a few rows of each dimension/fact table including all relevant columns. With that we can try to help you figure out how to adjust your model in detail.

General rule of thumb: You should always aim for a one-to-many relationship with uni-directional filter direction between your dimensions and fact table (with your dimensions filtering your fact table(s)). Avoid any many-to-many and bi-directional relationships whenever possible, unless you really know exactly what you're doing and why you need to do it in this particular case.

Think of it like this: Your relationships visualize how the filters flow through your data, and the data you really want to filter is always the data in your fact table, not the data in your dimensions (since the fact data is what you need to calculate with)! If your model is correctly set up, your interactivity happens automatically since the actual content of your KPIs is filtered by all dimensions in your model.

You should generally aim to get as close as possible to a star schema, but (realistically speaking) ~90% of the models I've encountered professionally so far were actually Snowflake schemas and still worked perfectly fine (don't officially quote me on that one - some purists might throw stones at me 🫣). Based on the numbers of rows you described, this sounds like a pretty small model, so while we should always aim for best practice modeling, you don't have to beat yourself up if you don't succeed in creating a perfectly clean star schema right away.

Regarding your requirements for 2) -> Could you describe in more detail which KPIs your showing, the differences between them and how exactly you'd want them to filter the other visuals? 

The possibilities depend on your exact requirements, but you could try and use the new card visual by adding any dimension as needed to the category-field + a field parameter as a slicer to switch between different Measures in your cards. Like that you can use your cards to cross-filter other visuals, but it's not the most user-friendly option (it might require quite a bit of conditional formatting to ensure that your users really know what they are currently cross-filtering whenever they click on a card). The better option would be to just use appropriate slicers on your page. 

[pankajnamekar25 · Accepted solution]

Hello @sree1610 

Keep Star Schema: Don’t merge tables. Keep Fact and Dimension tables separate.
Relationships: Create one-to-many (Dim → Fact) with single-direction filters.
Avoid Bi-Directional Filters: They cause ambiguity and slow performance.

Interactivity
Use Table visual (styled like a card) for clickable KPIs.
Or use buttons + bookmarks if you want card-style visuals to trigger filters.
Result: Clean model, fast performance, and fully interactive Report

If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.


Thanks,


Connect with me on:

LinkedIn