Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 |
Solved! Go to 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
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!
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
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
@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
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.
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
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
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
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
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!
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!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
104 | |
103 | |
87 | |
61 |
User | Count |
---|---|
162 | |
132 | |
131 | |
95 | |
86 |