Forum Discussion

Txtcher's avatar
Txtcher
Icon for Helper V rankHelper V
1 year ago
Solved

DAX Newbie Count Rows by Date Filter

I am using Excel Power Query / Data Model and I am brand new to DAX. I have 3 queries: Date_table CasesSent CasesClosed The CasesSent table has 2 dates, Sent Date & Due Date. The Cases Closed t...
Dax Help
  • Txtcher's avatar
    Txtcher
    1 year ago

    Posting to state that after much research, I have solved the problem.  I have one date table that has multiple relationships with the other table. When creating formulas involving "inactive" relationships, I needed to active that relationship.

    Example:  Cases Due:=CALCULATE(
    COUNTROWS('RS Cases'),USERELATIONSHIP(Date_Table[Date],'RS Cases'[Due By]))

FarhanJeelani's avatar
FarhanJeelani
Icon for Super User rankSuper User
1 year ago

HI  Txtcher ,

 

Try This

 

DAX
 

 

 

 

Backlog:= CALCULATE(
    COUNTROWS(
        FILTER(
            ALL('RS Cases'), -- Remove all filters from the entire table
            'RS Cases'[Due By] < TODAY() && -- Check if Due Date has passed
            (
                ISBLANK('RS Cases'[Entrance Date]) || -- Check if Entrance Date is blank
                'RS Cases'[Entrance Date] > 'RS Cases'[Due By] -- Check if Entrance Date is past Due Date
            )
        )
    )
)

 

 

Changes made:

  1. ALL('RS Cases'): Removed filters from the entire table instead of just the column. This is generally safer as it allows for broader context removal.

    Additional Considerations:

    • If you specifically want to keep filters on other columns while ignoring just the Entrance Date, you can adjust the logic depending on your needs.
    • If this doesn't resolve the error, please provide more details about the context in which this measure is being used, such as the specific error message or the model structure, so I can assist further!

 

 

 

Txtcher's avatar
Txtcher
Icon for Helper V rankHelper V
1 year ago

I as getting closer with this, but it still is not right. 

In my table, I have a Case Sent Date.  So in order to get a rolling count of overdue intakes, the Sent Date has to be between the table date, and then the entrance date would be null, or greater than the max sent date.  But, the count is still not working quite right with the following formula. It gets me the correct grand total, but not the monthly count ðŸ˜£

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

 

Also, the monthly count for cases due is not working correctly with this measure either:

Cases Due:=CALCULATE(
COUNTROWS(
FILTER(
'RS Cases',
'RS Cases'[Due By] >=MIN(Date_Table[Date]) &&
'RS Cases'[Due By] <=mAX(Date_Table[Date])
)
)
)

And I am trying to count the cases closed (if they have an entrance date, they are considered closed), but this is not giving me correct monthly counts either.

 

Frustrating! I am so close and I am completely stumped as to why this is not working.