How to Filter Measure by Selected Field in Pivot Table

[Txtcher]

I am new to DAX and the concepts of row vs filter context is way beyond my understanding and confusing as he$$.

I am using Excel Data Model and Power Pivot. The data model is very simple:

The active relationship between RS Cases and the Date Table is Sent to Region.

I have the following formula which works beautifully when calculating an overall CUMULATIVE count based on the month selected in the pivot:

CALCULATE(

COUNTROWS(RS_Cases),

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

)

)

But, if I add a new field to the pivot [Program], the count does not filter to adjust:

I tried creating a Program table, creating a relationship with RS_Cases and inserting a USERELATIONSHIP function, but that doesn't work with the FILTER(ALL...  

Any suggestions?  

And if I add slicers to this pivot, does that affect the way the measure is written?

[Txtcher · Accepted solution]

Problem solved after considerable research. This is how I modified the measure:

Backlog:=CALCULATE(

COUNTROWS(RS_Cases),

SUMMARIZE(RS_Cases,RS_Cases[Program]),

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

[Mahesh0016]

@Txtcher  I went through your problem statement, but I didn't understand what your end output should be. Can you elaborate precisely?

@Txtcher  Thank You!

[Txtcher]

The purpose of the report I am building is to track backlogged (overdue) cases.

Each case has the following dates and each of these fields has a relationship with the date table:

- Date Sent to Region

- Due By

- Entrance Date

- Status Change Date

Each case has a Status of either Open or Closed.

A case investigated timely would be: Status = Closed AND the Entrance Date <=Due By date.

The exception to this statement is that some cases are closed without an Entrance Date. In these instance we use the Status Change Date.

The goal is to create a monthly backlog table with a line chart. For example, how many cases were overdue in January 2024?

These are the 3 conditions that would count a case as backlog in January 2024:

1. Due By <=Jan 2024 && Status = "OPEN" ||

2. Due By <=Jan 2024 && Status = "CLOSED" && Entrance Date > Jan 2024 ||

3. Due By <=Jan 2024 && Status = "CLOSED" && IsBlank(EntranceDate) && Status Change Date >Jan 2024

The measure to obtain this count is:

CALCULATE(

COUNTROWS(RS_Cases),

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

The measure works perfectly to obtain the overall backlog count. The new twist is I now need the same count for each Program. So how do create a new measure that gives me the backlog count by Program? (I will keep the original measure.) I created a Program table with a relationship to the RS_Cases table. I tried adding the UseRelationship function (Program->Program) to the measure, but it has no effect. I think because that is in the CALCULATE part and not included in the FILTER part (sorry inelegant way of describing it but I'm green here). 

Also, I will want the user to be able to select the Program using a slicer (not sure if that affects how the measure is written).

Thanks for any help you can give.