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]))
Helper VThank you so much for the response. But that last expression is not working.
First, I changed my approach because I decided to combine Cases Sent and Cases Closed together into one table which is now called RS Cases.
To calculate the cases overdue (backlog), I tried modifying the formula as follows but it gives me an error:
Backlog:=CALCULATE(
COUNTROWS(
('RS Cases'),
FILTER(
('RS Cases'),
'RS Cases'[Due By]<TODAY() && --Check if Due Date has passed
(
ISBLANK('RS Cases'[Entrance Date]) || --Check if Entrance Date is blank (not closed)
'RS Cases'[Entrance Date]>'RS Cases'[Due By] -- or if Entrance Date is after Due Date
)
)
)
)
Also is this going to provide a count of cases that were overdue depending on the date filter of the pivot? (See my example in my op.)
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
)
- Txtcher
Hummm... I am getting an error. Here is what I wrote:
Backlog:=CALCULATE(COUNTROWS(FILTER('RS Cases','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)),ALL('RS Cases'[Entrance Date]) -- This ignores the Entrance Date filter in the current context))The error is highlighting the ALL expression. Syntax error?- 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:
- 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.