Forum Discussion
Modelling Multi valued attributes in dimensions in Power BI
Hi vividvimu
Yes you can use bidirectional relationships if you ensure that There's only one path between tables no ambiguity and The cardinality supports it (i.e., 1:* between Student and others).
Student[Student Id] → Student Programs[Student Id] (One-to-Many, Bidirectional)
Student[Student Id] → Student Language Proficiency[Student Id] (One-to-Many, Bidirectional)
Using bidirectional filters is fine if you have only one clear path between the tables. Avoid Circular relationships & Multiple paths between two tables (Power BI will disable relationships or throw ambiguity errors)
Large datasets where bi-directionality can cause performance issues
If bidirectional filtering is not safe or causes issues, use the UNION + TREATAS technique.
Hi vividvimu
- Avoid bi-directional relationships to prevent ambiguity and performance issues.Use one-way relationships from dimension tables (Program, Language, etc.) to the Student table. Let the Student table pass filters to downstream fact tables.
- Use TREATAS in measures to apply slicer filters from disconnected tables.
- Example measure using multiple TREATAS:
- Student Count with Filters =
CALCULATE(
DISTINCTCOUNT('Student'[StudentID]),
TREATAS(VALUES('Student Program'[StudentID]), 'Student'[StudentID]),
TREATAS(VALUES('Student Language'[StudentID]), 'Student'[StudentID]),
TREATAS(VALUES('Student Education'[StudentID]), 'Student'[StudentID])
)
- Use TREATAS to push slicer filters through the Student table.Prefer measures over relationships for combining multiple dimensions. Modularize DAX for reuse across fact tables.
- Test TREATAS with real data to check performance. Use calculation groups to simplify and avoid repeating complex DAX.
- Slicers from related tables don’t directly filter fact tables like Advising. Use measures to pass slicer filters to the Student table, then down to fact tables. TREATAS makes slicer values act as filters on Student[StudentID].
Hope this helps !!
Hi vividvimu
Just checking in to see if you had a chance to try the suggested approach using TREATAS for applying slicer filters from disconnected tables like Program, Language, or Education through the Student table.
As shared earlier:
- Avoid bi-directional relationships to prevent ambiguity and performance issues.
- Use one-way relationships from dimension tables to Student, and let Student filter downstream fact tables.
- TREATAS in DAX measures allows slicers on unrelated tables to act as filters on Student[StudentID].
Let me know if this approach worked well in your model or if you’re running into any issues I’d be happy to help troubleshoot or refine it further.