Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
DSiffredi
Microsoft Employee
Microsoft Employee

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

DSiffredi_1-1686846831584.png

 

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

 

 

2 ACCEPTED SOLUTIONS

You need to perform two joins forEach Date=>Lead Backup and BakupLead 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

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

DSiffredi
Microsoft Employee
Microsoft Employee

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! 

View solution in original post

10 REPLIES 10
ThxAlot
Super User
Super User

Conflict.pbix

 

ThxAlot_0-1688781187429.png

 

 

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 )
        )
    )

 

 

ThxAlot_0-1688777062298.png

 

For fun only, a showcase of powerful Excel formula,

 

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

 

ThxAlot_1-1688779439524.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



smpa01
Super User
Super User

@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

 

 

 

smpa01_0-1686850959910.png

 

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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
Microsoft Employee
Microsoft Employee

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 

DSiffredi_0-1686863016366.png

 

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

 

smpa01_1-1686862378610.png

 

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

 

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

You need to perform two joins forEach Date=>Lead Backup and BakupLead 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

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
DSiffredi
Microsoft Employee
Microsoft Employee

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

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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
DSiffredi
Microsoft Employee
Microsoft Employee

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
Microsoft Employee
Microsoft Employee

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! 

DSiffredi_0-1686869778589.png

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.