Forum Discussion
Microsoft EmployeeHow to detect conflicts on the same date - i.e. people going on vacations pointing at eachother
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 ternarysimply 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!
Community ChampionI 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
Community ChampionYou 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
DSiffredisimply 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
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