How To Create A Count With Multiple Filters

[Txtcher]

I have been tasked with coming up with a backlog report which provides a count of backlogged RS Cases by 2 different counts (Program and Region).

I have 3 tables:

A RS Case record contains a Sent to Region date, a Due Date, Status Change Date and a Status field.

A RS Case is considered backlog if any of the following conditions are true:

RS Case [Sent to Region] <> null && RS Case [Due Date] < Date_Table[Date] && RS Case [Status]="OPEN"
or
RS Case [Sent to Region] <> null && RS Case [Due Date] < Date_Table[Date] && RS Case [Status]="CLOSED" && RS Event [Survey Date] > Date_Table[Date] 
or
RS Case [Sent to Region] <> null && RS Case [Due Date] < Date_Table[Date] && RS Event [Survey Date] = null && RS Case [Status]="CLOSED" && RS Case [Status Change Date] > Date_Table[Date]

What we want is to create a line chart showing how the backlogged cases have progressed.

I know I can create a measure that summarizes the RS Cases table and then add filters, but I cannot add the field into the filter that refers to the RS Event table.

Is there a way to create this count? Or will I have to merge the tables into a single table to get the Survey Date? I wanted to avoid this because it takes forever to merge in PQ

Here is some sample data:

RS Case	RS Event ID	Region	Program	Received Date	Sent to Region	Status	Status Change Date	Due By
1006300		06	Nursing	04/29/2025	04/29/2025	OPEN	04/29/2025	05/17/2025
572025		06	Nursing	03/21/2025	03/24/2025	OPEN	03/24/2025	04/11/2025
568312		04	DAHS-ISSONLY	03/04/2025	03/05/2025	OPEN	03/05/2025	03/19/2025
1000621	a2heq000000HliTAAS	03	Assisted Living	04/01/2025	04/04/2025	CLOSED	05/22/2025	05/19/2025
567516	a2heq000000GkdBAAS	05	Assisted Living	02/27/2025	03/03/2025	CLOSED	05/05/2025	04/17/2025

RS Event ID	RS Event	Survey Date
a2heq000000HliTAAS	EVT-0000495238
a2heq000000GkdBAAS	EVT-0000493734	05/01/2025

See RS Case 567516. Its Due Date was 4/17/2025. The Survey Date was not until 5/1/2025. If we were counting backlog as of 4/30/2025, this case would be counted as backlog because the Survey Date was greater than 4/30/2025.

I thank you in advance if you can help.

[v-pnaroju-msft · Accepted solution]

Hi @Txtcher,

We appreciate your inquiry on the Microsoft Fabric Community Forum.

Based on my understanding, there is no requirement to flatten or merge tables in Power Query. The backlog report can be generated directly in Power BI using DAX and relationships.

Please find attached the screenshot and PBIX file which may assist in resolving the issue:

If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will help other community members facing similar queries.

Should you have any further questions, please feel free to contact the Microsoft Fabric community.

Thank you.