Forum Discussion

Txtcher's avatar
Txtcher
Helper V
1 year ago
Solved

Problems with Filter on Calculated Table

I am trying to create a table visual that displays records based on the "as-of-date."  I have a measure I have been using to calculate the count of records on the "as-of-date" and I think it is work...
Need Help
bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

Txtcher , Try using

DAX
Backlog =
VAR vSvyDate = LOOKUPVALUE('RS Events'[Survey Date], 'RS Events'[Id], SELECTEDVALUE('RS Cases'[RS Event ID]))
RETURN
CALCULATE(
COUNTROWS('RS Cases'),
FILTER(
ALL('RS Cases'),
('RS Cases'[Due By] <= MAX('Calendar'[Date]) && 'RS Cases'[Status] = "OPEN") ||
('RS Cases'[Due By] <= MAX('Calendar'[Date]) && 'RS Cases'[Status] = "CLOSED" && vSvyDate > MAX('Calendar'[Date])) ||
('RS Cases'[Due By] <= MAX('Calendar'[Date]) && ISBLANK(vSvyDate) && 'RS Cases'[Status] = "CLOSED" && 'RS Cases'[Status Change Date] > MAX('Calendar'[Date]))
)
)

 

DAX
Backlog Cases =
VAR vSvyDate = LOOKUPVALUE('RS Events'[Survey Date], 'RS Events'[Id], SELECTEDVALUE('RS Cases'[RS Event ID]))
RETURN
FILTER(
ALL('RS Cases'),
('RS Cases'[Due By] <= MAX('Calendar'[Date]) && 'RS Cases'[Status] = "OPEN" && NOT ISBLANK('RS Cases'[Due By])) ||
('RS Cases'[Due By] <= MAX('Calendar'[Date]) && 'RS Cases'[Status] = "CLOSED" && vSvyDate > MAX('Calendar'[Date]) && NOT ISBLANK('RS Cases'[Due By])) ||
('RS Cases'[Due By] <= MAX('Calendar'[Date]) && ISBLANK(vSvyDate) && 'RS Cases'[Status] = "CLOSED" && 'RS Cases'[Status Change Date] > MAX('Calendar'[Date]) && NOT ISBLANK('RS Cases'[Due By]))
)