Avoiding Bidirectional Relationships - I want to filter - Many to one

Power BI Data Model
1

Hi there
I have a model which works but can be very slow. I can’t seem to get my head around how to avoid bidirectional relationships.
I have created a simplified version below
I have a number of students. Each student has been at the school for many years at many levels
image
I want to be able to filter by year and year level in determining the average grade.
This works with the bidirectional filter as shown - But every day I watch a video that says “bidirectional is bad”
Should I just add Treatas to each relevant measure?
e.g.
Average Score = CALCULATE(AVERAGE(Grades[Score]),TREATAS(values(‘Year Level’[ID]),Students[ID]))

Can anyone suggest the best practice approach to dealing with this - either by model or Dax
Cheers
Mark

avoiding bidrectional.pbix (26.8 KB)

2

Hi @highschooteacher,

I’ve duplicated your model and changed the crossfilter setting to oneway (the one on the right).

Next I created a measure in which (for the duration of the calculation) I change the crossfilter setting to BOTH for that Relationship in the model.

Average Score v2 = 
    CALCULATE(
        AVERAGE( 'Grades (2)'[Score] ),
        CROSSFILTER( 'Students (2)'[ID], 'Year Level (2)'[ID], Both )
    )

Learn more about CROSSFILTER here.
Your sample file.
avoiding bidrectional.pbix (36.4 KB)

.
An alternative could also be denormalizing your model, bringing Year Level and Students together in a single table but I guess that will depend on the cardinality and number of attributes that involves…

I hope this is helpful

4 Likes
3

Thank you Melissa
This clearly works - I feel like I have lots of questions about the consequences of this and say using treatas instead or just leaving in the bidirectional. I will go away and rebuild my model with this and see if it improves the speed of the measures.
Thank you for your help
Mark