Forum Discussion

EWBWEBB's avatar
EWBWEBB
Helper III
3 years ago
Solved

USERELATIONSHIP ERROR - breaking results

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.

  • 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

2 Replies

  • 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's avatar
      EWBWEBB
      Helper III

      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?