Values replicated across dimension but relationship looks fine

[jjjppp]

Hi folks,

I'm mocking up a dataset to illustrate to someone how they might report on court cases, and the relationship between my two tables is not working. I've had a long day and my brain is fried, so maybe I'm missing something simple, but I just cannot figure out the issue.

This is what's happening to my viz: Average of Age measure is replicating across all members of Offense Type. Easy, right? It's probably a problem with the relationship.

Here are my relationship details

I know the values match in each column. I'm quite certain both are text type as well. Does anyone have an idea of why this relationship doesn't seem to be working? I know there are workarounds, but I feel like I'm going crazy as to why this is happening.

My apologies if I'm being dumb, but I've been staring at this for twenty minutes, and would like to just phone a friend and end my day. There's a link to a folder containing the file below. Thanks so much!

Link to file here: dax questions

[davehus · Accepted solution]

Hi @jjjppp , It's because the relationship type is 1 to many, if you set the cross filter to both, it should work but please validate the results you are getting.

[jjjppp]

Hi @davehus,

That did fix it. It makes sense now. I'm so used to using dimensions from the 1 side on the axis.

[jjjppp]

Hi @davehus,

Thanks for taking a look. I should have clarified--I know how to work around this using DAX or by merging the tables in Power Query. My question is why is happening in the first place when everything with the relationship seems in order. Any ideas?

Thanks!

[davehus]

Hi @jjjppp ,

Add a calculated column called Age to the case table. Age = Related(Age)

Average Age = AVERAGEX('cases','cases'[Age])

Please accept my answer if it is what you need.