Filtering a table on multiple criteria

[ReadTheIron]

I have two related tables, AssetTable and IncidentTable. I am trying to get a count of entries in FailureTable that satisfy multiple criteria. Specifically, I want to create a calculated column to count entries after a given date and excluding a given cause. Here, I want to count every incident except snow incidents after the asset was repaired.

Data examples:

AssetTable

Asset	RepairDate
Switch1	2/2/2022
Switch2	2/3/2022
Switch3	2/4/2022

IncidentTable

Asset	IncidentDate	Cause
Switch1	1/1/2022	Snow
Switch1	1/12/2022	Wind
Switch2	2/4/2022	Fog
Switch1	2/4/2022	Fog
Switch3	2/15/2022	Snow
Switch1	2/20/2022	Snow
Switch1	2/25/2022	Wind

Desired table

Asset	Date Repaired	QualifyingIncidentsAfterRepair
Switch1	2/2/2022	2
Switch2	2/3/2022	1
Switch3	2/4/2022	0

My current calculated column only counts the incidents after the repair.

IncidentsAfterRepair =

IF(ISBLANK('AssetTable'[RepairDate]),0,

COUNTX(

    FILTER(

        RELATEDTABLE(IncidentTable),'AssetTable'[RepairDate]<=IncidentTable[IncidentDate]),IncidentTable[IncidentDate])+0)

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula will work

=coalesce(CALCULATE(COUNTROWS(Incident),FILTER(Incident,Incident[Asset]=EARLIER(Asset[Asset])&&Incident[IncidentDate]>EARLIER(Asset[RepairDate])&&Incident[Cause]<>"snow")),0)

Hope this helps.

[ReadTheIron]

I'm writing this column for other asset tables, which I believe are formatted exactly the same way, but I'm getting "The first argument of EARLIER/EARLIEST is not a valid column reference in the earlier row context". I can't figure out why it would work on one and not the other. Any thoughts?

[ReadTheIron]

Never mind, found my error - the AssetRepairDate being referred to was a measure not a column. Changed it from a measure to a calculated column and now everything is working.

[ReadTheIron]

Thank you! This worked beautifully!

[Ashish_Mathur]

You are welcome.

[lbendlin]

You were close.

IncidentsAfterRepair = 
IF(ISBLANK([RepairDate]),0,
COUNTX(FILTER(RELATEDTABLE(IncidentTable),[RepairDate]<=[IncidentDate] && [Cause]<>"Snow" ),1)+0)

Are you sure you want this as a calculated column though?

[ReadTheIron]

I'm getting "too many arguments passed to the FILTER function. The maximum argument count for the function is 2"

I will eventually want to use totals from this, so I was looking for a column instead of a measure - if a measure would make more sense, I can give that a try.