Forum Discussion
Helper IIIFiltering a table on multiple criteria
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.
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.
7 Replies
- Ashish_Mathur
Super User
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.
ReadTheIronThank you! This worked beautifully!
- Ashish_Mathur
You are welcome.
- 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.
- 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.
