Forum Discussion
Helper VDAX Newbie Count Rows by Date Filter
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]))
Super User
It looks like there's an issue with the way COUNTROWS and FILTER are combined in the formula. In addition, to ensure that the measure is dynamic and responds to the date filter in your pivot, you’ll need to include context based on the filter context of the Date table.
Here’s a refined version of your Backlog measure:
Backlog =
CALCULATE(
COUNTROWS('RS Cases'),
FILTER(
'RS Cases',
'RS Cases'[Due By] < TODAY() &&
(
ISBLANK('RS Cases'[Entrance Date]) ||
'RS Cases'[Entrance Date] > 'RS Cases'[Due By]
)
),
ALL('RS Cases'[Entrance Date]) -- This ignores the Entrance Date filter in the current context
)
Helper VHummm... I am getting an error. Here is what I wrote:
- FarhanJeelani
HI Txtcher ,
Try This
DAXBacklog:= 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:
- 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
Thank you again for your patience and prompt response.
Unfortunately, the measure did not give me the count I want.
I want a rolling count of the number of intakes that were not worked from month to month. For example, as of Nov 1, 2023, how many cases were overdue? Then on Dec 1, 2023, how many overdue.
The measure when placed into the pivot table, did not provide the desired results:
- Txtcher
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.
- 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.