Link two date fields of the same table to the same field on the date table.

[TomEnns]

Hi everyone first time poster here, new to PowerBI, hoping someone can give me some pointers.

I have two tables in my data model

• Dates
  • Date
  • Day
  • Month
  • Year
• Cases
  • CaseNumber
  • CreatedDate
  • ClosedDate
  • Owner
  • TaskSet

Right now I have a link between the "'Case'[CreatedDate]" field and the "'Date'[Date]" Field.

I have three Elelements on my Screen: 

• Slicer : Linked to 'Date'[Date] field. 
• Clustered Bar Chart 1: New Cases By Owner
  
  • Axis: 'Cases'[Owner]
  • Values: Count of 'Cases'[CaseNumber] 
• Clustered Bar Chart 2: Closed Cases By Owner
  
  • Axis: 'Cases'[Owner]
  • Values: Count of 'Cases'[CaseNumber] 

As you can probably tell I am looking for one chart to display the number of new cases for the time period selected by the slicer and the second chart to display the number if closed cases in that same time period, both broken down by the case owners. As you can also probably tell I get the same values in each table as I am pulling and linking the exact same values. 

Is there a proper method to doing this? I am having the same issue in several places but this is a small example. I am trying to avoid having to pull in my entire dataset twice just to have one dataset for closed and one for new when they are referencing the same source. Any tips are appreciated. 

Thanks,

edited: Typos

[TomEnns]

Thanks Matt, worked perfectly. For anyone wondering for future reference. Read Matts article it was simple and explaine dthis perfectly. I ended up with two measures: 

Count of Created Cases

Count New Cases = 
CALCULATE(COUNT('Case'[CaseNum]), USERELATIONSHIP(Dates[Date], 'Case'[CaseCreatedDate])
)

Count of Closed Cases

Count Closed Cases = 
CALCULATE(COUNT('Case'[CaseNum]), USERELATIONSHIP(Dates[Date], 'Case'[CaseClosedDate])
)

I placed each of the measures into the values section of thier respective bar charts and it works great. 

[MattAllington · Accepted solution]

Read my article here, then post back any questions. https://exceleratorbi.com.au/multiple-relationships-between-tables-in-dax/