Forum Discussion

EWBWEBB's avatar
EWBWEBB
Icon for Helper III rankHelper 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...
datefilter
DAX
USERELATIONSHIP
  • Uspace87's avatar
    Uspace87
    3 years ago

    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
Uspace87's avatar
Uspace87
Icon for Resolver III rankResolver III
3 years ago

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
Icon for Helper III rankHelper III
3 years ago

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?