Relations between tables not working as intended

[JBDelvaux]

Greetings,

I have an issue with a relation not being done properly. For a basic understanding of the issue, in the following image you can see the two tables at work. For every shipment we can have multiples TaskGroup (usually 1 or 2 and very rarely more than 2).

I am simply trying to know, for each users, how many shipment they have participated to. Doesn't matter if they were alone, or others, just know how many shipments they were a part of.

To that extent, I simply created a table with two columns, one being user_ID and the other being the count of BOL. Every single user gets the exact same number of BOL. I'm pretty sure something is going wrong, but I can't quite put my finger on it.

Thank you for your time and have a nice day

[Anonymous · Accepted solution]

Hi @JBDelvaux ,

The shipment of Table 1 is the bol of Table 2? If so, isn't count of bol the count of shipment? Why not directly use userid and count of shipment to create the table?

The reason for the same count of bol is that the direction of the relation is single from Table 1 to Table 2. Changing the direction to both can solve the problem.

Best Regards,

Jay

[JBDelvaux]

That was it, thank you.

Both bol and shipment could be used interchangeably, but since I was counting the number of shipments, I was using the value in the shipment table. Raw human logic rather than actually thinking of performance and avoid using an extra table.

For userID, it's simply because it would take longer for the user to find the users after having their ID. Ids in that case are just a number, so they don't easily tell who it was.

As for the issue, I was simply to focused on other detail to see that I didn't put it in a two way filtering. Thank you

[amitchandak]

@JBDelvaux , the relationship seems correct. Please check the source(of column)  in visual is correct. Hop user_id is not coming from another table

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[JBDelvaux]

Yep, can confirm, it is indeed the right avlues from the right tables.

Here's a sample of relevant datas directly from the DB source, I did not put names, nor client since everything used is sensitive data. I doubt that the number of a BOL without knowing the company nor our client could cause issue though, so I kept it to relevant data that do not share private informations.

1st column is BOL in shipment. 2nd is The date the shipment left our place. 3rd is the BOL as recorded in the Taskgroup. Taskgroup_ID is the ID of the taskgroup (only name I renamed for clarity. It's original name is "id"). user_id is the ID of the person that did the taskgroup.

Something that surprise me even more is that it somewhat works for another one of my pages. That other page is working (somewhat, I have an issue with filtering by user, which I believe is related to my current issue). Hiding client names ofc. User names are vague enough that I don't think it can cause issue.  User name is found in another table linked to taskgroup via the user_id.

For that visual I added "UserRelated" that is a created column in the "taskgroup" table

UserRelated = RELATED('picking user'[nom])

And "List of UserRelated" which is a measure

List of UserRelated values = 
VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('picking task_group'[UserRelated])
VAR __MAX_VALUES_TO_SHOW = 3
RETURN
	IF(
		__DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW,
		CONCATENATE(
			CONCATENATEX(
				TOPN(
					__MAX_VALUES_TO_SHOW,
					VALUES('picking task_group'[UserRelated]),
					'picking task_group'[UserRelated],
					ASC
				),
				'picking task_group'[UserRelated],
				", ",
				'picking task_group'[UserRelated],
				ASC
			),
			", etc."
		),
		CONCATENATEX(
			VALUES('picking task_group'[UserRelated]),
			'picking task_group'[UserRelated],
			", ",
			'picking task_group'[UserRelated],
			ASC
		)
	)

Thank you for trying to help and have a nice day 😉
Delvaux Jean-Baptiste