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.

v-aatheeque's avatar
v-aatheeque
Community Support
1 year ago

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

vividvimu's avatar
vividvimu
Frequent Visitor
1 year ago

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.
 
Thanks 
  • v-aatheeque's avatar
    v-aatheeque
    Community Support
    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.