Max used in True/False that is used as a table filter expression

[Txtcher]

I am using Excel power pivot/Data Model.

I have 2 tables: Date table and a RS Cases table (data table).

There are multiple relationships between the Date table and the RS Cases table so I need to use the function USERELALTIONSHIP when writing the measure.

I am trying to solve an events in progression problem. Specifically:  Total count of cases overdue each month for the past year. 

By way of explanation, if I was writing an "IF" statement it would look like this:  if Due By >=[Date_table] and Entrance Date is null or Entrance Date>Date_table, then True, else False.

I am getting a semantic error for the following measure and am asking for help fixing it.

Backlog2:=CALCULATE(
COUNTROWS('RS Cases'),
USERELATIONSHIP(Date_Table[Date],'RS Cases'[Due By]),
'RS Cases'[Due By]<Max(Date_Table[Date]) &&
(
ISBLANK('RS Cases'[Entrance Date]) ||
'RS Cases'[Entrance Date]>Max(Date_Table[Date])
)
)

Looking at this I think there are filters I do not need. Do I need Due By <Max(Date_Table[Date]) if I am using that relationship?

or the last filter for Entrance Date >Max(date_table[date])?

If I remove those filters, the error goes away but the count is not correct.

I am new to DAX measures and it is hard to wrap my head around using the date table.

Any assistance would be very much appreciated. Thanks!

[FreemanZ]

hi @Txtcher ,

try like:

Backlog2:=

CALCULATE(

    COUNTROWS('RS Cases'),

    USERELATIONSHIP(

        Date_Table[Date],

        'RS Cases'[Due By]

     ),

    FILTER(

    ALL('RS Cases'[Due By], 'RS Cases'[Entrance Date]),

    'RS Cases'[Due By]<Max(Date_Table[Date]) &&

      (

       ISBLANK('RS Cases'[Entrance Date]) 

        || 'RS Cases'[Entrance Date]>Max(Date_Table[Date])

    ))

)

[Txtcher]

Thank you. The measure is not producing errors, but is not producing correct data. The monthly counts are too low when I compare them with a query in which I created a custom column:  

if [Due By]<=#date(2024,9,2) and ([Entrance Date]=null or ([Entrance Date]>=#date(2024,9,2))) then "Backlog" else "Not Backlog"

When I filter this column for "Backlog," the Aug 2024 count is: 4,910.  

But the pivot has 1,066: