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.
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.
Hi v-aatheeque ,
Thank you so much for your reply! Apologies for my delayed response.
I intially tired all the measures with using treatas. But, the problem is not all students have language records and few other similar cases. If I use TREATAS directly for all measures, the student ids that are required also gets filtered out as they are not present in language table. So, I ended up rewriting the measure to check if a filter is applied on a program or language column or both to filter those student ids or else take all the student ids.
No. of Students =IF (ISFILTERED('Student Programs') && ISFILTERED('Student Language Proficiency'),CALCULATE (COUNTROWS(Student),TREATAS(VALUES('Student Programs'[Student Id]), Student[Student Id]),TREATAS(VALUES('Student Language Proficiency'[Student Id]), Student[Student Id])),IF (ISFILTERED('Student Programs'),CALCULATE (COUNTROWS(Student),TREATAS(VALUES('Student Programs'[Student Id]), Student[Student Id])),IF (ISFILTERED('Student Language Proficiency'),CALCULATE (COUNTROWS(Student),TREATAS(VALUES('Student Language Proficiency'[Student Id]), Student[Student Id])),COUNTROWS(Student))))The measures keep getting complicated. It doesnt work like a natural join in the desired direction.If I ensure that I dont have multiple paths in my model to reach from one table to another, can I go ahead with bidirectional relationship. I would need bi-directional between Student <-> Student Program and Student <-> Student Language.ThanksHi 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.