Blanks to 0s and relationship lead to incorrect table visualization (wrong number of rows)

[sebastien_druon]

Hello community,

I have the following tables

Users

User ID	Name	Team ID
1	User 1	1
2	User 2	2
3	User 3	2

Teams

Team ID	Team
1	T1
2	T2

Usage

User ID	Date	Usage
1	10/10/2020	5
1	12/11/2020	3
2	09/09/2020	5
2	15/11/2020	8
3	19/10/2020	2
3	23/11/2020	4

As well as a calendar table containing among others Date, and YearMonth (YYYY-MM)

The relationships are the following:

- Users[Team ID] -> Team[Team ID]

- Usage[User ID] -> Users[User ID]

- Usage[Date] -> Calendar[Date]

I am using the following measure to display usage data:

# Usage No Blanks = VAR USG = SUM(Usage[Usage]) RETURN IF(ISBLANK(USG), 0, USG)

I have a problem getting the data correctly in a table visualization when using the Team Name from the Team table: the relationship between Team and User seems to be completely ignored. When using the Team ID from the user table instead, it works as expected:

Case 1 using 'Team'[Team]: too many rows, relationship between Team and Users ignored

'Users'[Name] | 'Calendar[YearMonth] | 'Team'[Team] | 'Usage'[# Usage No Blanks]

Name	YearMonth	Team	# Usage No Blanks
User 1	2020-09	T1	0
User 1	2020-09	T2	0
User 1	2020-10	T1	5
User 1	2020-10	T2	0
User 1	2020-11	T1	3
User 1	2020-11	T2	0
User 2	2020-09	T1	0
User 2	2020-09	T2	5
User 2	2020-10	T1	0
User 2	2020-10	T2	0
User 2	2020-11	T1	0
User 2	2020-11	T2	8
User 3	2020-09	T1	0
User 3	2020-09	T2	0
User 3	2020-10	T1	0
User 3	2020-10	T2	2
User 3	2020-11	T1	0
User 3	2020-11	T2	4

Case 2 using 'Users'[Team ID]: expected behaviour

'Users'[Name] | 'Calendar[YearMonth] | 'Users'[Team ID] | 'Usage'[# Usage No Blanks]

Name	YearMonth	Team ID	# Usage No Blanks
User 1	2020-09	1	0
User 1	2020-10	1	5
User 1	2020-11	1	3
User 2	2020-09	2	5
User 2	2020-10	2	0
User 2	2020-11	2	8
User 3	2020-09	2	0
User 3	2020-10	2	2
User 3	2020-11	2	4

Any help understanding where the problem is coming from would be highly appreciated 🙂

A link to the pbix file https://we.tl/t-b2GatTk3er

Thanks a lot!

Sebastien

[v-easonf-msft]

Hi, @sebastien_druon 

Could you please tell me whether your problem has been solved?
If yes, you could accept the helpful answer as solution.  For now, there is no content of description in the thread. If you still need help, please share more details to us.

Best Regards,
Community Support Team _ Eason

[AllisonKennedy]

Both are working correctly, it's just that Team ID 1 for user 3 doesn't exist in your Usage table, so it's not displayed with the 0. It does exist in the Team table, so it displays all possible Team values, for every user. Just a zero if they aren't in Usage. You could try pulling Team name into the User table using the RELATED() function if you don't want to see all teams for every user.