Modelling Multi valued attributes in dimensions in Power BI

[vividvimu]

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 many multi valued attributes like enrolled in dual programs, languages known, Various exam scores, work experience records. In the report, the user need to filter other facts by program name, admit year, languages etc,.

Example: Below tables are part of one scenario. Multiple such cases exist in the model due to other multi-valued attributes of student

1. Student - general student demograohics

2. Student Program - Student program enrollment (same student can have multiple records)

3. Counselling session details (fact) - Has only student id (no program id link)

Just like counselling session details, all the other facts also have only student id as a link.

Relationships :

Student -> Student Program - One to many

Student -> Counselling session details - One to many

How should we model this scenario such a way that I can filter the facts through multi valued attributes like program without breaking any of the modelling rules. The report will also have rls based on the Program name. 

If I make the relationship between student program and student bi directional and create a program dimension with 1 to many relationship to student program, I can achieve what I want to do in the report. But is that a recommended approach? will there be any risks when we scale the model?

I would like to use the best approach possible such that the model is scalable and also understandable to power users who would be given build permission.

Thank you very much for your time in advance!

#datamodelling

[vividvimu]

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. 

[vividvimu]

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.  

[v-aatheeque]

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 !!

[gmsamborn]

Hi @vividvimu 

I would like to help but I'm a little unclear about your requirements.

Can you provide the following?

1)  Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

2) Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

3) Please explain how you would expect to get from step 1 to 2.

4) If possible, please show your past attempts at a solution.

 A .pbix file with sample data would be best.

[v-aatheeque]

Hi @vividvimu 

I wanted to send a quick reminder regarding previous request for clarification on your requirements. To better assist you, could you please provide the following information?

Your input will greatly help in addressing your concerns more effectively.

Thank you for your cooperation, and I look forward to your response!

[Elena_Kalina]

Hi @vividvimu 

For attributes like programs, languages, and exam scores—where a single student can have multiple values—you’ll need a bridge table. For example:

Student → StudentProgramBridge ← Program

This bridge table should include composite keys (StudentID, ProgramID) and any relevant relationship attributes (e.g., start date, end date).

Since counselling sessions only link to StudentID, keep the direct relationship between Student and CounsellingSession. But if you need to filter counselling sessions by program, you can:

• Use the bridge table in your DAX measures

• Create a virtual relationship in DAX using TREATAS() or similar techniques

This keeps the model clean while allowing the filtering you need.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[ajaybabuinturi]

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.