Forum Discussion
Relationship issue - Fact and DIM
Hi Anonymous ,
I am using 5 facts and 3 dim tables in a report and the slicers should come from the dim, so while building the relationship, I tried to connect with dim to fact with one to many relationship. But, there were nulls persent in the dim(s), This is the first approach.
second approach: Have taken specific dimensional columns from the DIMs and appended and tried to create a master diim table by removing duplicates and nulls, have given one to many relationship between them and triued using slicers . But, slicers have shown blanks and not gving correct output for other selected slicer values also.
third approach: Have taken the main dimensional columns from the FACT tables and appended them and tried to create a MASTER DIM table, rmoved duplicated, removed nulls from dim columns from the fact tables, appended and made 1 to many relation between master dim and fact tables, but still facinng the issue of blanks and values not giving correct output
Please help and let me know your thoughts and solution
Fix your original DIM tables - remove nulls/duplicates in Power Query
Connect each DIM (1) to all 5 FACTs (many) using the natural key columns.
Slicers on DIM tables work across all facts automatically.
Keep dims separate, clean, unique.
Hi BharathS1307 ,
1.Clear dimension table in power query: Remove nulls and duplicates
2. Handle nulls in fact tables.
3. Use one to many relationship from dim to fact (cross filter single and relationship active)
Your 2nd and 3rd approach seems ambigous and filtering may not propagate correctly🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
đź’ˇ Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
đź”— Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!
If a slicer from a dimension shows (Blank) or gives inconsistent filtering across multiple fact tables, the most common causes are:
-
keys in the fact table that do not exist in the dimension
-
null/blank keys in the fact tables
-
many-to-many style behavior introduced by the “master dim” logic
-
5 Replies
- Kedar_Pande
Super User
Fix your original DIM tables - remove nulls/duplicates in Power Query
Connect each DIM (1) to all 5 FACTs (many) using the natural key columns.
Slicers on DIM tables work across all facts automatically.
Keep dims separate, clean, unique.
- grazitti_sapna
Hi BharathS1307 ,
1.Clear dimension table in power query: Remove nulls and duplicates
2. Handle nulls in fact tables.
3. Use one to many relationship from dim to fact (cross filter single and relationship active)
Your 2nd and 3rd approach seems ambigous and filtering may not propagate correctly🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
đź’ˇ Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
đź”— Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!
- cengizhanarslan
If a slicer from a dimension shows (Blank) or gives inconsistent filtering across multiple fact tables, the most common causes are:
-
keys in the fact table that do not exist in the dimension
-
null/blank keys in the fact tables
-
many-to-many style behavior introduced by the “master dim” logic
-
v-pnaroju-msftCommunity Support
Thankyou, Kedar_Pande, grazitti_sapna and cengizhanarslan for your responses.
Hi BharathS1307,
We appreciate your inquiry through the Microsoft Fabric Community Forum.
We would like to inquire whether have you got the chance to check the solutions provided by Kedar_Pande, grazitti_sapna and cengizhanarslan to resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.
Thank you.- v-pnaroju-msft
Hi BharathS1307,
We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.
Thank you.