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.

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.