How to detect conflicts on the same date - i.e. people going on vacations pointing at eachother

[DSiffredi]

hi team, I have one that I've been scratching my head for quite a bit. 
I need to be able to detect conflicts when a person is taking time off pointing to another person that is also taking time off (on the same dates)
In other words: I need to create a logic to identify when a conflict occurs with the data we have below

Conflict = IF on the same day the person (letter) on column Lead = to the person (letter) on column Backup then TRUE

this is the desired result and as you can imagine I don't have a conflict column or calculation just yet :
I'm attaching a picture of the desired results, as well as a table with the data so you don't have to create the scenarios to test the logic. 
thank you so much in advance to whomever can sort this out ! 🙂 

date	lead	backup	desired result - not to use in PBI
6/1/2023	A	B	TRUE
6/1/2023	B	A	TRUE
6/1/2023	C	D	FALSE
6/1/2023	E	F	FALSE
6/1/2023	J	K	FALSE
6/2/2023	A	B	TRUE
6/2/2023	B	A	TRUE
6/2/2023	C	D	FALSE
6/2/2023	E	F	FALSE
6/5/2023	A	B	FALSE
6/5/2023	C	D	FALSE
6/5/2023	E	F	FALSE
6/6/2023	C	D	FALSE
6/6/2023	J	K	FALSE
6/6/2023	E	F	FALSE
6/7/2023	A	B	TRUE
6/7/2023	B	C	TRUE
6/7/2023	E	F	FALSE
6/7/2023	G	H	FALSE
6/8/2023	A	B	TRUE
6/8/2023	H	J	FALSE
6/8/2023	E	F	FALSE
6/8/2023	C	A	TRUE

[smpa01 · Accepted solution]

You need to perform two joins forEach Date=>Lead ∪ Backup and Bakup∪Lead to find the intersection in each. Any join that returns match then it is true

fullOuter = 
VAR dt =
    MAX ( 'Table 1'[date] )
VAR _lead =
    MAX ( 'Table 1'[lead] )
VAR _backup =
    MAX ( 'Table 1'[backup] )
VAR tblOne_1 = { _lead }
VAR tblOne_2 = { _backup }
VAR tblTwo_1 =
    SELECTCOLUMNS (
        SUMMARIZE (
            FILTER ( ALL ( 'Table 1' ), 'Table 1'[date] = dt ),
            'Table 1'[backup]
        ),
        "@candidate", [backup] & ""
    )
VAR tblTwo_2 =
    SELECTCOLUMNS (
        SUMMARIZE (
            FILTER ( ALL ( 'Table 1' ), 'Table 1'[date] = dt ),
            'Table 1'[lead]
        ),
        "@candidate", [lead] & ""
    )
VAR tblthree_1 =
    GENERATE (
        tblTwo_1,
        VAR one = [@candidate] RETURN FILTER ( tblOne_1, [Value] = one )
    )
VAR tblthree_2 =
    GENERATE (
        tblTwo_2,
        VAR one = [@candidate] RETURN FILTER ( tblOne_2, [Value] = one )
    )
VAR ternary_1 =
    COUNTX ( tblthree_1, [@candidate] )
VAR ternary_2 =
    COUNTX ( tblthree_2, [@candidate] )
VAR ternary =
    IF ( ternary_1 <> BLANK () || ternary_2 <> BLANK (), 1, 0 )
RETURN
    ternary

[ThxAlot]

Conflict.pbix

Chk = 
VAR __dt = AGMT[date]
VAR __ld = AGMT[lead]
VAR __bk = AGMT[backup]
RETURN
    NOT ISEMPTY(
        FILTER(
            AGMT,
            AGMT[date] = __dt && ( AGMT[lead] = __bk || AGMT[backup] = __ld )
        )
    )

For fun only, a showcase of powerful Excel formula,

=COUNT(0/(([date]=[@date])*(([backup]=[@lead])+([lead]=[@backup]))))>0

[smpa01]

@AlexisOlson  Is there a reason why COUNTX is incorrect here where TOCSV gives me the correct result.

ALso @DSiffredi  did you mean `

Conflict = IF on the same day letter on column B = to letter on column C then TRUE

and vice versa?`

Measure = 
VAR dt =
    MAX ( 'Table 1'[date] )
VAR _lead =
    MAX ( 'Table 1'[lead] )
VAR tblOne =
    SUMMARIZE (
        FILTER ( ALL ( 'Table 1' ), 'Table 1'[date] = dt ),
        'Table 1'[date],
        'Table 1'[lead]
    )
VAR jn =
    FILTER (
        SUMMARIZE (
            FILTER (
                tblOne,
                ('Table 1'[date],'Table 1'[lead])
                    IN (
                        SUMMARIZE (
                            FILTER ( ALL ( 'Table 1' ), 'Table 1'[date] = dt ),
                            'Table 1'[date], 'Table 1'[backup]
                        )
                    )
            ),
            [lead]
        ),
        [lead] IN { _lead }
    ) 
//produces incorrect result        
VAR cal =
    COUNTX ( jn, [lead] ) 
//returns same table correctly
VAR csv =
    TOCSV ( jn, -1, "," )
RETURN
    csv

[AlexisOlson]

It seems like a bug when using a negative MaxRows argument in TOCSV. @jeffrey_wang said it was fixed but maybe it's still an issue?

Try changing -1 to 10 and notice that the results change even though there is never more than 1 row in this situation.

[DSiffredi]

I just rephrased the definition of conflict to hopefully make it more clear. 
Conflict = IF on the same day the person (letter) on column Lead = to the person (letter) on column Backup then TRUE

First of all thank you! although I don't think this is accomplishing what it needs to do. 
Index 23 . it doesn't detect the conflict as you can see 

[smpa01]

I don't think this is beyond DAX but let's just put your desired result through test.

You are asking DAX to check on a given day, whether a particular lead also exists as a backup on the list of backups for that particular day (correct me if I am wrong). If this is true, the last row does not seem correct? Please conf

If the desired result is deemed to be wrong, try this out

Measure2 = 
VAR dt =
    MAX ( 'Table 1'[date] )
VAR _lead =
    MAX ( 'Table 1'[lead] )
VAR tblOne = { _lead }
VAR tblTwo =
    SELECTCOLUMNS (
        SUMMARIZE (
            FILTER ( ALL ( 'Table 1' ), 'Table 1'[date] = dt ),
            'Table 1'[backup]
        ),
        "@candidate", [backup] & ""
    )
VAR tblthree =
    GENERATE (
        tblTwo,
        VAR one = [@candidate] RETURN FILTER ( tblOne, [Value] = one )
    )
VAR debugger =
    TOCSV ( tblthree, -1, "," )
VAR ternary =
    IF ( COUNTX ( tblthree, [@candidate] ) == 1, TRUE (), FALSE () )
RETURN
    ternary

[smpa01 · Accepted solution]

You need to perform two joins forEach Date=>Lead ∪ Backup and Bakup∪Lead to find the intersection in each. Any join that returns match then it is true

fullOuter = 
VAR dt =
    MAX ( 'Table 1'[date] )
VAR _lead =
    MAX ( 'Table 1'[lead] )
VAR _backup =
    MAX ( 'Table 1'[backup] )
VAR tblOne_1 = { _lead }
VAR tblOne_2 = { _backup }
VAR tblTwo_1 =
    SELECTCOLUMNS (
        SUMMARIZE (
            FILTER ( ALL ( 'Table 1' ), 'Table 1'[date] = dt ),
            'Table 1'[backup]
        ),
        "@candidate", [backup] & ""
    )
VAR tblTwo_2 =
    SELECTCOLUMNS (
        SUMMARIZE (
            FILTER ( ALL ( 'Table 1' ), 'Table 1'[date] = dt ),
            'Table 1'[lead]
        ),
        "@candidate", [lead] & ""
    )
VAR tblthree_1 =
    GENERATE (
        tblTwo_1,
        VAR one = [@candidate] RETURN FILTER ( tblOne_1, [Value] = one )
    )
VAR tblthree_2 =
    GENERATE (
        tblTwo_2,
        VAR one = [@candidate] RETURN FILTER ( tblOne_2, [Value] = one )
    )
VAR ternary_1 =
    COUNTX ( tblthree_1, [@candidate] )
VAR ternary_2 =
    COUNTX ( tblthree_2, [@candidate] )
VAR ternary =
    IF ( ternary_1 <> BLANK () || ternary_2 <> BLANK (), 1, 0 )
RETURN
    ternary

[DSiffredi]

quick question so I learn from this.
What's the meaning of these two variables?
VAR tblOne_1 = { _lead }
VAR tblOne_2 = { _backup }

[smpa01]

When you put curly bracket around a scalar value it becomes a single row table

[DSiffredi · Accepted solution]

simply put , GENIOUS! thank you very much, I had that on my mind but wasn't able to translate it to DAX. thank you very much! 

[DSiffredi]

thanks! answering your question on how this is true.

check row 17 and 18 ... A is pointing to B and on the same day B is pointing to someone else so as a result B shouldn't be backing up A in row 17. It's a bit tricky but these things happen and why I'm building this report with all your help. 
similar case on row 21: A is pointing to B meaning A is on vacation and pointing to B as back up , now on row 24 , C is taking vactions and pointing to A which is already on vacations - so can't be something we can approve. 

reason why I did it this way is because it's not regular but things like may happen so I want to catch them before we get there. 😉 

thanks again!