Forum Discussion

ReadTheIron's avatar
ReadTheIron
Helper III
4 years ago
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...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    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.

lbendlin's avatar
lbendlin
Super User
4 years ago

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's avatar
ReadTheIron
Helper III
4 years ago

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.