Forum Discussion

Txtcher's avatar
Txtcher
Helper V
1 year ago
Solved

Help With DAX - Filter UserRelationship CountRows

I am using the data model in Excel, not Power BI. And, I am brand new to DAX. I have a data table and a calendar table.  The purpose of the report I am building is to track backlogged (overdue) cas...
Need Help
  • Txtcher's avatar
    Txtcher
    1 year ago

    Thank you for your response. Late yesterday, I discovered if I removed the USERRELATIONSHIP function from the statement below, I achieved the result I was after:

    Backlog:=CALCULATE(
    COUNTROWS(RS_Cases),
    USERELATIONSHIP(Date_Table[Date], RS_Cases[Due By]),
    FILTER(
    ALL(RS_Cases),
    (RS_Cases[Due By]<=MAX(Date_Table[Date]) && RS_Cases[Status]="OPEN") ||
    (RS_Cases[Due By]<=MAX(Date_Table[Date]) && RS_Cases[Entrance Date]>Max(Date_Table[Date])) ||
    (RS_Cases[Due By]<=MAX(Date_Table[Date]) && ISBLANK(RS_Cases[Entrance Date]) && RS_Cases[Status]="CLOSED" && RS_Cases[Status Change Date]>MAX(Date_Table[Date]))
    )
    )

     

rajendraongole1's avatar
rajendraongole1
Super User
1 year ago

Hi Txtcher - can you try below logic with both userrelationship function and filter:

 

Backlog :=
CALCULATE(
COUNTROWS(RS_Cases),
USERELATIONSHIP(Date_Table[Date], RS_Cases[Due By]),
FILTER(
RS_Cases,
(RS_Cases[Due By] <= MAX(Date_Table[Date]) && RS_Cases[Status] = "OPEN") ||
(RS_Cases[Due By] <= MAX(Date_Table[Date]) && NOT(ISBLANK(RS_Cases[Entrance Date])) && RS_Cases[Entrance Date] > MAX(Date_Table[Date])) ||
(RS_Cases[Due By] <= MAX(Date_Table[Date]) && ISBLANK(RS_Cases[Entrance Date]) && RS_Cases[Status] = "CLOSED" && RS_Cases[Status Change Date] > MAX(Date_Table[Date]))
)
)

 

I hope this works

Txtcher's avatar
Txtcher
Helper V
1 year ago

Unfortunately, I am still getting a blank result.