Relationship between 2 tables with a Date Table and creating link between the two data tables.

[Anonymous]

I have two tables and am using a date table to create my measures but need to be able to cross interact between the two main data tables.  I have one Product table that shows the Start-up Date for each product model.  My second table shows when each product/product model had a revision.  There can be multiple revisions for one product.  My main goal is to create a Calcualtion of the **bleep** products by start up date divided by a rolling 12 months on the product revisions.  My calculations seem to work but I need to be able to add a legend for the products and/or a slicer for the products but since the two data tables are only connected to the date table, they don't interact with each other and I can't add the product field to the visual.  I don't know how to fix this either in my measures or in my data model.  Here are my measures:

1) SumCount = Count(Products[Start Up Date])

2) CumProductCount =Var _curcount = MAX('Date'[Date])

Var _t = FILTER(ALLSELECTED(Products), Products[Start Up Date] <= _curcount)

Return

SUMX(_t,[SumCount2])

3) RevisionCountR12 =

var Maxdate = MAX('Date'[Date])

Return

CALCULATE(

    COUNT('Product Revisions'[Revision Date]),

    FILTER(ALL('Date'),

        AND(Date'[Date] <= Maxdate,

       DATEADD('Date'[Date],1, YEAR) > Maxdate )))

4) MTBC = DIVIDE([CumProductCount], [RevisionCountR12))

Here is the data below:

ID	Product	Start Up Date	Model
63	E	7/7/2012	E63
64	E	7/7/1996	E64
65	E	7/7/2019	E65
66	E	7/7/2017	E66
67	E	9/7/1954	E67
68	E	8/15/1971	E68
69	E	8/15/1971	E69
70	E	8/15/1971	E70
71	E	8/15/2000	E71
72	E	8/15/1980	E72
73	E	8/15/1971	E73
74	E	8/7/1994	E74
75	E	4/29/1996	E75
76	E	7/7/2012	E76
113	N	7/7/2005	N113
114	N	1/7/1997	N114
115	N	4/7/2019	N115
116	N	1/7/1997	N116
117	N	3/8/2013	N117
118	N	7/7/2020	N118
119	N	3/8/2013	N119
120	N	3/8/2013	N120
121	N	3/8/2013	N121
122	N	3/8/2012	N122
123	N	7/7/1992	N123
124	N	3/8/2013	N124
126	N	3/8/2013	N126
272	E	9/7/1954	E272
273	E	8/15/1971	E273
333	O	3/8/2018	O333
334	O	3/8/2018	O334
335	O	3/8/2018	O335
336	O	9/7/2020	O336
337	O	3/8/2017	O337
338	O	3/8/2020	O338

ID	Product	Revision Date	Model
75	E	4/15/2006	E75
64	E	7/7/2012	E64
74	E	8/7/2012	E74
75	E	7/7/2014	E75
64	E	7/7/2016	E64
68	E	10/22/2017	E68
70	E	10/22/2017	E70
69	E	10/22/2017	E69
74	E	9/7/2018	E74
63	E	7/7/2019	E63
64	E	7/7/2020	E64
75	E	7/7/2020	E75
65	E	8/2/2020	E65
69	E	5/16/2021	E69
67	E	2/14/2023	E67
272	E	2/14/2023	E272
74	E	9/7/2023	E74
123	N	1/8/2010	N123
117	N	10/15/2018	N117
114	N	3/8/2020	N114
117	N	9/14/2020	N117
126	N	4/23/2022	N126
126	N	9/14/2022	N126
334	O	7/25/2019	O334
333	O	3/12/2020	O333
334	O	12/13/2020	O334

[Sahir_Maharaj]

Hello @Anonymous,

To resolve this challenge, consider the following steps:

1. Create a direct relationship between the Product table and the Product Revisions table based on a shared key. This would likely be the Model or ID column.

2. If required, modify your measures to leverage the direct relationship to make sure they calculate as expected.

3. Adjust the visual interactions if necessary. If you have a slicer on the Product/Model and you want it to impact both tables (hence all visuals based on them), the direct relationship will facilitate this. Make sure the slicer is set to "Single" select or ensure your measures handle multi-selection appropriately.

Should you require my further assistance, please do not hesitate to reach out to me.

[Anonymous]

So which relationship would be active and which would not.  I can easily connect the two data tables by the ID.  I assume this one would be active and I would have a dotted line between one of my data tables and the date table?