UseRelationship Not Working as Expected

[Anonymous]

Hi, I will try and explain the situation as best as i can. I can't see why what I am doing isn't working. 

My "master data" table has every race which in the end pushes out a calulated "TimeRating" for that run for the particular runner. This is stored in one table. 
I then have a second table loaded in which is say tomorrows fields for the races. So I have created a relationship between the runner name between "master data" and "race meeting" table. I then calculate the average "Time Rating" for that runner in the master database with this "CALCULATE(AVERAGE(MasterData[TimeRating])" Pretty simple. 

Now what I want to do it calculate the average "TimeRating" for the particular distance for that particular dog. So I have my relationship between the Names so that is all good and working. So I create a relationship between the Track in "MasterData" and in "RaceMeeting." which is many to many which i assume is part of the problem. Obviously this is inactive.

Then i try and do my calculation 

CALCULATE(AVERAGE(MasterData[TimeRating]),USERELATIONSHIP(MasterData[Track],RaceMeeting[Track]))

When I put this in a table I would expect this value to be filtered by track but it doesn't do anything, The average for "TimeRating" remains the same. Any ideas? Open to any suggestions on work arounds. I am assuming i can't do it this way. 

[harshnathani]

Hi @Anonymous ,

I am assuming that your Master Data has

1. NAme of Runners (Dogs).

2. Track Name (i.e 50m, 100m, etc)

3. Time Rating (i.e Time the runner took at each of these races)

And you want to Calculate Avg time the runner took for each of the tracks.

CALCULATE(AVERAGE(MasterData[TimeRating]),ALLEXCEPT(MasterData, MasterDate[Track],MasterData[RunnerName]))

Pull the runner name from Master Table, Track from master table and the measure above.

Incase this is not the scenario, pls share your relationships and sample Data.

Thanks,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

In case it did not help, please provide additional information and mark me with @ so that I can investigate further

[Greg_Deckler]

Does your inactive relationship have the right direction?

[Anonymous]

Yeah I played around with that and it didn't change anything