Forum Discussion
Solved
Filtering 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 co...
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.
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.
Thank you! This worked beautifully!
You are welcome.
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?
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.
