FILTER NOT CONTAIN in calculate DAX Function

[Hemansi]

I needed your help with a DAX measure. I am trying to link two tables that unfortunately does not have primary or foreign key. I have Call Data and Sales Data and I need to match every sale to the calls that were sale. Here is the code I've used;

CallID =

    --Filter Dialler Data with Time Ranges

    VAR _2MinRange = FILTER(WinbackRawData,AND(WinbackRawData[Time]<=(JEMData[Time]+TIME(0,2,0)),WinbackRawData[Time]>=(JEMData[Time])-TIME(0,2,0)))

    VAR _5MinRange = FILTER(WinbackRawData,AND(WinbackRawData[Time]<=(JEMData[Time]+TIME(0,5,0)),WinbackRawData[Time]>=(JEMData[Time])-TIME(0,5,0)))

    VAR _10MinRange = FILTER(WinbackRawData,AND(WinbackRawData[Time]<=(JEMData[Time]+TIME(0,10,0)),WinbackRawData[Time]>=(JEMData[Time])-TIME(0,10,0)))

    VAR _15MinRange = FILTER(WinbackRawData,AND(WinbackRawData[Time]<=(JEMData[Time]+TIME(0,15,0)),WinbackRawData[Time]>=(JEMData[Time])-TIME(0,15,0)))

    --Lookup CallID for matching AgentName, Date, PhoneNumber constraining Classifications to Sales

    VAR _CallID1 =

        LOOKUPVALUE(

            WinbackRawData[CallID],

            WinbackRawData[AgentName],JEMData[AgentName],

            WinbackRawData[Date], JEMData[Date],

            WinbackRawData[PhoneNumber],JEMData[PhoneNumber],

            WinbackRawData[ClassificationType],"Sales",0)

    --Lookup CallID for matching AgentName, Completed Date&Time, PhoneNumber constraining AgentClassifications to Completed

    VAR _CallID2 =

        CALCULATE(    

            FIRSTNONBLANK(WinbackRawData[CallID], 1),

            FILTER(WinbackRawData,JEMData[AgentName]=WinbackRawData[AgentName]),

            FILTER(WinbackRawData,JEMData[C.Date]=WinbackRawData[Date]),

            FILTER(WinbackRawData,JEMData[PhoneNumber]=WinbackRawData[PhoneNumber]),

            FILTER(WinbackRawData, WinbackRawData[AgentClassification]="Completed"),

            FILTER(WinbackRawData,AND(WinbackRawData[Time]<=(JEMData[C.Time]+TIME(0,10,0)),WinbackRawData[Time]>=(JEMData[C.Time])-TIME(0,10,0))))

    VAR _Result1 = IF(_CallID1=0,_CallID2,_CallID1)

    --Get FirstCallID for matching AgentName, Date and Time with 4 mins Range constraining Classifications to Sales

    VAR _CallID2MinRange =

        CALCULATE(

            FIRSTNONBLANK(WinbackRawData[CallID],1),

            FILTER(WinbackRawData,NOT(CONTAINSROW({_Result1},WinbackRawData[CallID]))),

            FILTER(WinbackRawData,JEMData[AgentName]=WinbackRawData[AgentName]),

            FILTER(WinbackRawData,JEMData[Date]=WinbackRawData[Date]),

            FILTER(WinbackRawData, WinbackRawData[ClassificationType]="Sales"),

            _2MinRange)

    --Get FirstCallID for matching AgentName, Date and Time with 10 mins Range constraining Classifications to Sales

    VAR _CallID5MinRange =

        CALCULATE(

            FIRSTNONBLANK(WinbackRawData[CallID],1),

            FILTER(WinbackRawData,NOT(CONTAINSROW({_Result1},WinbackRawData[CallID]))),

            FILTER(WinbackRawData,JEMData[AgentName]=WinbackRawData[AgentName]),

            FILTER(WinbackRawData,JEMData[Date]=WinbackRawData[Date]),

            FILTER(WinbackRawData, WinbackRawData[ClassificationType]="Sales"),

            _5MinRange)

    --Get FirstCallID for matching AgentName, Date and Time with 20 mins Range constraining Classifications to Sales

    VAR _CallID10MinRange =

        CALCULATE(

            FIRSTNONBLANK(WinbackRawData[CallID],1),

            FILTER(WinbackRawData,NOT(CONTAINSROW({_Result1},WinbackRawData[CallID]))),

            FILTER(WinbackRawData,JEMData[AgentName]=WinbackRawData[AgentName]),

            FILTER(WinbackRawData,JEMData[Date]=WinbackRawData[Date]),

            FILTER(WinbackRawData, WinbackRawData[ClassificationType]="Sales"),

            _10MinRange)

    --Get FirstCallID for matching AgentName, Date and Time with 30 mins Range constraining Classifications to Sales

    VAR _CallID15MinRange =

        CALCULATE(

            FIRSTNONBLANK(WinbackRawData[CallID],1),

            FILTER(WinbackRawData,NOT(CONTAINSROW({_Result1},WinbackRawData[CallID]))),

            FILTER(WinbackRawData,JEMData[AgentName]=WinbackRawData[AgentName]),

            FILTER(WinbackRawData,JEMData[Date]=WinbackRawData[Date]),

            FILTER(WinbackRawData, WinbackRawData[ClassificationType]="Sales"),

            _15MinRange)

    VAR _Result2 = IF(_Result1=BLANK(),_CallID2MinRange,_Result1)

    VAR _Result3 = IF(_Result2=BLANK(),_CallID5MinRange,_Result2)

    VAR _Result4 = IF(_Result3=BLANK(),_CallID10MinRange,_Result3)

    VAR _Result5 = IF(_Result4=BLANK(),_CallID15MinRange,_Result4)

RETURN

    _Result5

However, I keep getting duplicate values and I want every variable to lookup value that was not an outcome already from previous result. I have tried using the highlighted red text above but it keeps giving me error, Is there anything else I could use or do?

[Mahesh0016 · Accepted solution]

@Hemansi   FILTER(WinbackRawData,NOT (WinbackRawData[CallID]  IN{_Result1} ))
For E.g :
Price =
CALCULATE (
SUM ( Bike[Price] ),
FILTER ( Bike, NOT ( Bike[Color] IN { "XX", "YY" } ) )
)

Please try this.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.