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]))
    )
    )

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi Txtcher ,

 

Could you please provide sample data? It will be better If there is a pbix file(does not contain sensitive data). This will help us reproduce the problem and provide solution.

 

 

Best regards,

Mengmeng Li

  • Txtcher's avatar
    Txtcher
    Helper V
    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]))
    )
    )