USERELATIONSHIP ERROR - breaking results

[EWBWEBB]

I am trying to count the number of cases closed and logged in a date range using a DimDate calendar in my slicer.

DimDate is a Simple calendar table

Date,DateKey,Day,Month,Year

I then have a CentralTracker Table which contains all my cases.

PersRef,DateLogged,DateClosed,DateLogKey,DateClosedKey

I have relationships between
DimDate[DateKey] (1) to (*) CentralTracker[DateLogKey] - Active

DimDate[DateKey] (1) to (*) CentralTracker[DateClosedKey] - InActive

In nearly all instances I filter by Date Logged so it just works.

However, I would also like to just count the number of cases closed in the selected range, regardless of the date logged.

If I use the following piece of DAX, without filtering, I get the correct count of cases closed. But I can't filter this and get the cases clsoed ignoring the date logged.

VAR MinDate = MIN(DimDate[Date])
VAR MaxDate = MAX(DimDate[Date])

VAR Result = 
CALCULATE(
    COUNTROWS(Central_Tracker),
    FILTER(Central_Tracker,
        Central_Tracker[Date Case Closed] >= MinDate &&
        Central_Tracker[Date Case Closed] <= MaxDate
    )
)

RETURN
Result

Using this DAX I get 187 cases, which is correct.

When I Introduce USERELATIONSHIP into this to try and get it to filter by the close date rather than the date logged it breaks.

VAR MinDate = MIN(DimDate[Date])
VAR MaxDate = MAX(DimDate[Date])

VAR Result = 

CALCULATE(
    COUNTROWS(Central_Tracker),
    USERELATIONSHIP(Central_Tracker[DateClosedKey],DimDate[DateKey]),
    FILTER(Central_Tracker,
        Central_Tracker[Date Case Closed] >= MinDate &&
        Central_Tracker[Date Case Closed] <= MaxDate
    )
)

RETURN
Result

Using this, again with no filters I get 11.

I would expect that without any filters applied that the two values would be the same.

[Uspace87 · Accepted solution]

Try this one:

VAR MinDate = MIN(Central_Tracker[DateClosedKey])
VAR MaxDate = MAX(Central_Tracker[DateClosedKey])

VAR Result = 

CALCULATE(
    COUNTROWS(Central_Tracker),
    USERELATIONSHIP(Central_Tracker[DateClosedKey],DimDate[DateKey]),
    FILTER(DimDate,
        DimDate[DateKey] >= MinDate &&
        DimDate[DateKey] <= MaxDate
    )
)

RETURN
Result

[EWBWEBB]

Amazing, thank you this has worked but I don't get why?

In my head having the Variables as minimum and maximum date pulled from the central tracker doesn't make sense as I want the Min & Max date from the date table filter and then all closed dates that fall between this.

Any chance you can entertain me and explain why this works?