Matrix Rows - Related table showing all values, rather than just the related one

[thepuzzlemaster]

I have 2 related tables (1: many from Users[userId] -> Homes[ownerId]):
Users:

usersId	name	email
123	john smith	john@smith.com
124	becky riley	becky@riley.com

Homes:

ownerId	homeId	capacity
123	1	2
123	2	4
124	3	6

I also have a 3rd related table (1:many from Homes[homeId] -> Availability[homeId])

homeId	date	date: month
1	12/12/2022	12/01/2022
1	01/10/2023	01/01/2023
1	01/12/2023	01/12/2023
2	01/10/2023	10/01/2023

I have created a matrix, and I want to display the email address associated with a specific owner, but instead it is showing me every owner's email address under each ownerId when I also include the availability data.

I added the following under rows:
Homes[ownerId],
Users[email],
Homes[homeId],

And the following under Values:
Homes[capacity]

I see the following as expected:

ownerId	email	homeId	capacity
123	john@smith.com	1	2
		2	4
124	becky@riley.com	3	6

But if I put Availability[date: month] on columns and a measure which aggregates the availability data on values I get:

ownerId	email	homeId	December 2022	January 2023
123	john@smith.com	1	1	2
		2	0	1
	becky@riley.com	1	1	2
		2	0	1
124	john@smith.com	3	0	0
	becky@riley.com	3	0	0

If I remove the Users[email] from rows I get the expected result:

ownerId	homeId	December 2022	January 2023
123	1	1	2
	2	0	1
124	3	0	0

I imagine there is just some fundamential misunderstanding I have about something here. But I'm confused as to why the inclusion of that aggregated measure would cause the data displayed in the rows to get all wonky.

Thanks!

[lbendlin]

It is important to carefully choose which column to take from which table. Generally you want to use the higher level dimension colums first.

see attached

[thepuzzlemaster]

Thank you so much for the reply. Apologies for not having fully recreated my issue in a similfied pbix that I could share. I forgot an important detail that I didn't realize was necessary here. 

In my actual visual, I am using a measure as my value, not just an aggregation. I've updated your sample .pbix file with a similar measure to my production report, so that it is reproing my issue. I also added some additional users to the users table, since there are some users who may not have a home, to make it match my real data more closely.

It looks like maybe I can't upload a file yet (fairly new to this forum). I am trying to share via google drive.
Hopefully this works. https://drive.google.com/file/d/1B7tX8-r9I9fhfssf4Cccf464jpeoxkjZ/view?usp=sharing

[lbendlin · Accepted solution]

Explicit measures require a filter context.  You are trying to report on things that aren't there (gaps in your data).  That requires cross joins and / or disconnected tables. You can also use COALESCE to handle some situations.

unavailable/month = COALESCE(DISTINCTCOUNT(Availability[date]), 0)

see attached.

[thepuzzlemaster]

Thanks again for the reply and the additional context of what is going on here.

What I ended up doing, which gives me the result I want, is to create an additional column (ownerEmail) on my "Homes" table, which uses a merged query to pull in the email address of the owner from the users table. 

The part that is still confusing to me, is given the relationship I set up between Homes[ownerId] and Users[userId], why isn't the visual recognizing the related email field from the user table when the prior row is ownerId and displaying just the related email? Is this specific to the way a matrix works in particular? Is there a term I could search for in order to read up about this for a more in-depth explanation of why the extra column on the Homes table is necessary to get this to work the way I want it to?

[lbendlin]

search for "auto exist"