Mostly written by the great Appster of course.
IFS(
AND(
OR(
CONTAINS([Unit Type], "5000"),
CONTAINS([Unit Type], "W5"),
CONTAINS([Unit Type], "7000"),
CONTAINS([Unit Type], "W7"),
CONTAINS([Unit Type], "9000"),
CONTAINS([Unit Type], "W9"),
CONTAINS([Unit Type], "11000"),
CONTAINS([Unit Type], "15000"),
CONTAINS([Unit Type], "W15"),
CONTAINS([Unit Type], "I15"),
CONTAINS([Unit Type], "16000"),
CONTAINS([Unit Type], "W16"),
CONTAINS([Unit Type], "I16")
),
ISNOTBLANK([UF])
),
IF(
AND(
EOMONTH(
DATE(
CONCATENATE([Install Year], "-", [Install Month], "-01")
),
0
) <= EOMONTH(TODAY(), -36),
CONTAINS([UF], "UF"),
NOT(CONTAINS([Unit Type], "ICE")),
NOT(
IN(
"5 Filters",
SELECT(
Running Filter Change List[Filters Changed],
AND(
[Serial #] = [_THISROW].[Serial #],
[Date] > EOMONTH(TODAY(), -36)
)
)
)
)
),
"ALL 5",
"Base 3"
),
AND(
OR(
CONTAINS([Unit Type], "5000"),
CONTAINS([Unit Type], "W5"),
CONTAINS([Unit Type], "7000"),
CONTAINS([Unit Type], "W7"),
CONTAINS([Unit Type], "9000"),
CONTAINS([Unit Type], "W9"),
CONTAINS([Unit Type], "11000"),
CONTAINS([Unit Type], "15000"),
CONTAINS([Unit Type], "W15"),
CONTAINS([Unit Type], "I15"),
CONTAINS([Unit Type], "16000"),
CONTAINS([Unit Type], "W16"),
CONTAINS([Unit Type], "I16")
),
ISBLANK([UF])
),
IF(
OR(
[High TDS Alert],
AND(
EOMONTH(
DATE(
CONCATENATE([Install Year], "-", [Install Month], "-01")
),
0
) <= EOMONTH(TODAY(), -60),
EOMONTH(TODAY(), -60) >= MAX(
SELECT(
Running Filter Change List[Date],
AND(
CONTAINS([Filters Changed], "5 Filters"),
[Serial #] = [_THISROW].[Serial #]
)
)
)
)
),
"ALL 5",
"Base 3"
),
CONTAINS([Unit Type], "ICE"),
"Ice Filter",
OR(
CONTAINS([Unit Type], "I12"),
CONTAINS([Unit Type], "W12")
),
IF(
OR(
[High TDS Alert],
EOMONTH(TODAY(), -60) >= MAX(
SELECT(
Running Filter Change List[Date],
AND(
CONTAINS([Filters Changed], "5 Filters"),
[Serial #] = [_THISROW].[Serial #]
)
)
)
),
"ALL 5",
"Base 2"
),
AND(
CONTAINS([Unit Type], "FQ"),
ISBLANK([UF])
),
IF(
OR(
[High TDS Alert],
EOMONTH(TODAY(), -36) >= MAX(
SELECT(
Running Filter Change List[Date],
AND(
CONTAINS([Filters Changed], "4 Filters"),
[Serial #] = [_THISROW].[Serial #]
)
)
)
),
"ALL",
"Base 3"
),
AND(
CONTAINS([Unit Type], "FQ"),
ISNOTBLANK([UF])
),
IF(
EOMONTH(TODAY(), -24) >= MAX(
SELECT(
Running Filter Change List[Date],
AND(
CONTAINS([Filters Changed], "4 Filters"),
[Serial #] = [_THISROW].[Serial #]
)
)
),
"ALL",
"Base 3"
)
)