Relationship issues part 2

[CMSGuy]

Here is the scenario.  I have three tables of data related by EmployeeID and transaction date.  I figured out how to show data in a visual table, but now I have some missing data based on data from other tables.  Here is a sample of some data and what I am seeing and what I need to see.  If you notice, the data from the first table is good.  The data from the second table is good.  The data from the third table is missing May 4.  Is that because there are no entries to the first / second table(s)?  

	Task 1	Task 2	Task 3	Task 4
1-May	1		3	4
2-May	1	2
3-May
4-May

	Hours	Vac	Sick
1-May	8
2-May	8
3-May		8
4-May

	Rate1	Rate2	Rate3
1-May	3	3	3
2-May	3	3	3
3-May
4-May	3	3	3

What is being shown

	Task 1	Task 2	Task 3	Task 4	Hours	Vac	Sick	Rate1	Rate2	Rate3
1-May	1		3	4		8		3	3	3
2-May	1	2				8		3	3	3
3-May							8
4-May

What is needed to be shown

	Task 1	Task 2	Task 3	Task 4	Hours	Vac	Sick	Rate1	Rate2	Rate3
1-May	1		3	4		8		3	3	3
2-May	1	2				8		3	3	3
3-May							8
4-May								3	3	3

This is the current relation of these tables

Thanks for any help you can give

[NaveenGandhi]

Hello @CMSGuy 

I have used the same dataset and relationship as you have provided. This shouldn't cause any issue for this requirement. Relationship is based on transaction date.

The date from the below table visual is from calendar table.

Please provide more information if you still feel the issue is not solvable. 

Regards,

Naveen 

If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

[CMSGuy]

I do have that extra relation in there of EmployeeID.  I wonder if I removed that if it would "fix" the issue?

[NaveenGandhi]

That should not be of any impact in the current context, as you are not using any columns from that table in the visual or filter, which might impact the underlying data for the three tables.

[CMSGuy]

In recreating what you did using my datasets, I did find that the Ratings "number" is being summarized.  When I clicked Do Not Summarize, I lost that other line of data.  When I do summarize, it is there.  Should I change those rating numbers into Text as they do not need any sums or calculations?  

[NaveenGandhi · Accepted solution]

Summarising should do the work, if you change the change datatype to text and there is multiple entries for same date, it can only show first or last value.

[CMSGuy]

That did seem to do the trick. Thank you for your help