Forum Discussion

vividvimu's avatar
vividvimu
Frequent Visitor
1 year ago
Solved

Modelling Multi valued attributes in dimensions in Power BI

Dear community,   I have Career Services analytics data model where there are many facts like internships, counselling session details etc. The main dimension is student but the student has got man...
Need Help
  • v-aatheeque's avatar
    v-aatheeque
    1 year ago

    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.

ajaybabuinturi's avatar
ajaybabuinturi
Super User
1 year ago

Hi vividvimu,
Wanted to let you know that you can create a virtual relationship using TREATAS DAX function. This is the only option to filter the other fact tables. Rather than relying on bidirectional relationships use Single-direction relationships (from dimension to attribute table) and filter propagation via DAX using TREATAS.

 

Example of TREATAS DAX Logic:

Counselling Count by Program =
CALCULATE(COUNTROWS('Counselling Sessions'),
TREATAS( VALUES('Counselling Sessions'[Program ID]), 
        'Student'[Program ID]
       )        
)


More info about TREATAS: 

https://www.sqlbi.com/articles/physical-and-virtual-relationships-in-dax/ 
https://learn.microsoft.com/en-us/dax/treatas-function-dax 

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.