Forum Discussion

DSiffredi's avatar
DSiffredi
Icon for Microsoft Employee rankMicrosoft Employee
3 years ago
Solved

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

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 ! 🙂 

 

dateleadbackupdesired result - not to use in PBI
6/1/2023ABTRUE
6/1/2023BATRUE
6/1/2023CDFALSE
6/1/2023EFFALSE
6/1/2023JKFALSE
6/2/2023ABTRUE
6/2/2023BATRUE
6/2/2023CDFALSE
6/2/2023EFFALSE
6/5/2023ABFALSE
6/5/2023CDFALSE
6/5/2023EFFALSE
6/6/2023CDFALSE
6/6/2023JKFALSE
6/6/2023EFFALSE
6/7/2023ABTRUE
6/7/2023BCTRUE
6/7/2023EFFALSE
6/7/2023GHFALSE
6/8/2023ABTRUE
6/8/2023HJFALSE
6/8/2023EFFALSE
6/8/2023CATRUE

 

 

Need Help
  • smpa01's avatar
    smpa01
    3 years ago

    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's avatar
    DSiffredi
    3 years ago

    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! 

10 Replies

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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

     

     

     

     

     

     

     

    • DSiffredi's avatar
      DSiffredi
      Icon for Microsoft Employee rankMicrosoft Employee
      3 years ago

      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's avatar
        smpa01
        Icon for Community Champion rankCommunity Champion
        3 years ago

        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

         

         

         

         

    • AlexisOlson's avatar
      AlexisOlson
      Icon for Super User rankSuper User
      3 years ago

      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.

  • ThxAlot's avatar
    ThxAlot
    Icon for Super User rankSuper User
    3 years ago

    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