Match Category =
VAR Pipeline_ID_Count_SPPID =
CALCULATE(
COUNTROWS('Main2'),
ALLEXCEPT('Main2', 'Main2'[Pipeline_ID]),
CONTAINSSTRING('Main2'[File], "SPPID")
)
VAR Pipeline_ID_Count_S3D =
CALCULATE(
COUNTROWS('Main2'),
ALLEXCEPT('Main2', 'Main2'[Pipeline_ID]),
CONTAINSSTRING('Main2'[File], "S3D")
)
VAR Max_Nominal_Diameter_SPPID =
CALCULATE(
MAX('Main2'[MAX NOMINAL DIAMETER]),
ALLEXCEPT('Main2', 'Main2'[Pipeline_ID]),
CONTAINSSTRING('Main2'[File], "SPPID")
)
VAR Max_Nominal_Diameter_S3D =
CALCULATE(
MAX('Main2'[MAX NOMINAL DIAMETER]),
ALLEXCEPT('Main2', 'Main2'[Pipeline_ID]),
CONTAINSSTRING('Main2'[File], "S3D")
)
VAR Dia_Mismatch =
IF (
ISBLANK(Max_Nominal_Diameter_S3D) || ISBLANK(Max_Nominal_Diameter_SPPID),
FALSE(),
Max_Nominal_Diameter_S3D <> Max_Nominal_Diameter_SPPID
)
RETURN
SWITCH(
TRUE(),
Pipeline_ID_Count_SPPID = 0 && Pipeline_ID_Count_S3D > 0, "ONLY IN S3D",
Pipeline_ID_Count_SPPID > 0 && Pipeline_ID_Count_S3D = 0, "ONLY IN SPPID",
Pipeline_ID_Count_SPPID > 0 && Pipeline_ID_Count_S3D > 0 && Max_Nominal_Diameter_SPPID = Max_Nominal_Diameter_S3D,
"Full Match",
Pipeline_ID_Count_SPPID > 0 && Pipeline_ID_Count_S3D > 0 && Max_Nominal_Diameter_SPPID <> Max_Nominal_Diameter_S3D,
"Line Match but Dia Mismatch",
"UNKNOWN"
)
