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.
Thank you gmsamborn , v-aatheeque , Elena_Kalina , ajaybabuinturi for your time and recommendations. Unfortunately I am unable to provide any sample data at the moment. I will provide more details about the use case and also share my model here. This is a migration use case from another BI tool. Hope this helps. I have so far added the below tables into the model. But still there are more facts.
I did try to use TREATAS in the measure. But I may have to use multiple TREATAS experessions to consider the relatiosnhip between many of the mutlivalued attributes. Because the report pages have slicers from student Program, Student Language apart from student table fields etc., The user will use this for operational reporting. Hence there are so many tables which will use the fields from 2 or more of the top tables and also the bottom fact tables. eg: sudent document details will have student attributes + Student program + Student document details. Another table will have student details, program + student career counselling session details. I tried bi-directional relationship between student and its attributes and it didnt work well in some cases. I am not sure if it is good idea to use multiple TREATAS expressions between student and its attributes in the same measure and how scalable it will be as there will more facts getting added to this. Thank you all in advance for your time and effort. Really appreciate it and looking forward to a recommended modelling solution for this scenario.
Additional Info. In the model, all the relatiosnhips are through student id. The fact tables do not have any program id or details related to the multi valued attributes.
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.