Forum Discussion
Matrix Rows - Related table showing all values, rather than just the related one
I have 2 related tables (1: many from Users[userId] -> Homes[ownerId]):
Users:
| usersId | name | |
| 123 | john smith | [email protected] |
| 124 | becky riley | [email protected] |
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 | homeId | capacity | |
| 123 | [email protected]m | 1 | 2 |
| 2 | 4 | ||
| 124 | [email protected] | 3 | 6 |
But if I put Availability[date: month] on columns and a measure which aggregates the availability data on values I get:
| ownerId | homeId | December 2022 | January 2023 | |
| 123 | [email protected]m | 1 | 1 | 2 |
| 2 | 0 | 1 | ||
| [email protected] | 1 | 1 | 2 | |
| 2 | 0 | 1 | ||
| 124 | [email protected] | 3 | 0 | 0 |
| [email protected] | 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!
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.
5 Replies
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
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=sharingExplicit 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.
