Date Table Issue

[obriaincian]

I have created a date table in Query Editor using the below code, converted it to a table and then changed data type to date

= List.Dates(#date(2018,12,31), 365*8, #duration(1,0,0,0))

I have another table called Test with an ID column and 2 calculated columns (2 calculated columns look up dates from a third table).

I have created relationships from the Test table to the date table (I have linked 2 of the calculated columns in the test table to the date table).

The issue I'm having is when I create a table visual with the Months and Year from the date table and add the counts of the dates from the 2 calculated columns in the Test table they are shownig the same counts for the 2 calculated columns.

Can someone please help, I have been stuck on this for quite awhile.

Tried to attach a sample file but no luck. Heres some Screenshots

Thanks

[obriaincian · Accepted solution]

The issue here was only one relationship could be active between the Sheet1 table and teh Date Table at any one time.

The active relationship in my case was between the Date1 in the Sheet1 table and the Dates in the Date Table.

Solution was to adjust the Sheet1Date2 measure and call out which relationship should be used. See below

Sheet1Date2 = 
CALCULATE(
COUNTX(GROUPBY(Sheet1,Sheet1[ID]), COUNT(Sheet1[Date2])),
USERELATIONSHIP(Sheet1[Date2],'Date Table'[Dates]))