Forum Discussion
uif19085
Helper III
3 years agoFlag duplicates with DAX
Hello, i'm trying to find a way to flag first apprence of a duplicates across muliple columns using DAX, my table looks like this: ID DATE FLAG 1 01/01/2011 1 ...
- 3 years ago
uif19085 can you please test out the following meaure , you probably need a rowNum/Index Column
dup = VAR __val = CALCULATE ( [current], OFFSET ( -1, DISTINCT ( ALL ( 'Table' ) ), ORDERBY ( 'Table'[Index] ), KEEP, PARTITIONBY ( 'Table'[id] ) ) ) VAR currentId = MAX ( 'Table'[id] ) VAR currentIndex = MAX ( 'Table'[Index] ) VAR dateCount = CALCULATE ( DISTINCTCOUNT ( 'Table'[date] ), FILTER ( ALL ( 'Table' ), 'Table'[id] = currentId && 'Table'[Index] <= currentIndex ) ) VAR final = SWITCH ( TRUE (), __val == BLANK (), 1, [current] - 1 = __val && dateCount = 1, 0, 1 ) RETURN finalor like this
Measure = VAR currDate = MAX ( 'Table'[date] ) VAR currDate2 = currDate + 0 VAR currentId = MAX ( 'Table'[id] ) VAR currentIndex = MAX ( 'Table'[Index] ) VAR minIndexbyID = CALCULATE ( [currentIndex], INDEX ( 1, DISTINCT ( ALL ( 'Table' ) ), ORDERBY ( 'Table'[Index] ), KEEP, PARTITIONBY ( 'Table'[id] ) ) ) VAR filterTbl1 = FILTER ( ALL ( 'Table' ), 'Table'[id] = currentId && 'Table'[Index] >= minIndexbyID && currentIndex >= 'Table'[Index] ) VAR filterTbl2 = FILTER ( ALL ( 'Table' ), 'Table'[id] = currentId && 'Table'[Index] >= minIndexbyID ) VAR leftTbl1 = SELECTCOLUMNS ( filterTbl1, "dt", 'Table'[date] + 0 ) VAR leftTbl2 = SELECTCOLUMNS ( filterTbl2, "dt", 'Table'[date] + 0 ) VAR rightTbl = SELECTCOLUMNS ( { currDate }, "dt", [Value] + 0 ) VAR dates1 = NATURALINNERJOIN ( leftTbl1, rightTbl ) VAR dates2 = NATURALINNERJOIN ( leftTbl2, rightTbl ) VAR val1 = COUNTROWS ( dates1 ) VAR val2 = COUNTROWS ( dates2 ) VAR ternary = SWITCH ( TRUE (), val1 = 1 && val2 = 1, val1 & "-unique entry", val1 = 1 && val2 <> 1, val1 & "-has duplicate entry", "0" & "-has duplicate entry" ) RETURN ternary
smpa01
Community Champion
3 years agouif19085 can you please test out the following meaure , you probably need a rowNum/Index Column
dup =
VAR __val =
CALCULATE (
[current],
OFFSET (
-1,
DISTINCT ( ALL ( 'Table' ) ),
ORDERBY ( 'Table'[Index] ),
KEEP,
PARTITIONBY ( 'Table'[id] )
)
)
VAR currentId =
MAX ( 'Table'[id] )
VAR currentIndex =
MAX ( 'Table'[Index] )
VAR dateCount =
CALCULATE (
DISTINCTCOUNT ( 'Table'[date] ),
FILTER (
ALL ( 'Table' ),
'Table'[id] = currentId
&& 'Table'[Index] <= currentIndex
)
)
VAR final =
SWITCH (
TRUE (),
__val == BLANK (), 1,
[current] - 1 = __val
&& dateCount = 1, 0,
1
)
RETURN
final
or like this
Measure =
VAR currDate =
MAX ( 'Table'[date] )
VAR currDate2 = currDate + 0
VAR currentId =
MAX ( 'Table'[id] )
VAR currentIndex =
MAX ( 'Table'[Index] )
VAR minIndexbyID =
CALCULATE (
[currentIndex],
INDEX (
1,
DISTINCT ( ALL ( 'Table' ) ),
ORDERBY ( 'Table'[Index] ),
KEEP,
PARTITIONBY ( 'Table'[id] )
)
)
VAR filterTbl1 =
FILTER (
ALL ( 'Table' ),
'Table'[id] = currentId
&& 'Table'[Index] >= minIndexbyID
&& currentIndex >= 'Table'[Index]
)
VAR filterTbl2 =
FILTER (
ALL ( 'Table' ),
'Table'[id] = currentId
&& 'Table'[Index] >= minIndexbyID
)
VAR leftTbl1 =
SELECTCOLUMNS ( filterTbl1, "dt", 'Table'[date] + 0 )
VAR leftTbl2 =
SELECTCOLUMNS ( filterTbl2, "dt", 'Table'[date] + 0 )
VAR rightTbl =
SELECTCOLUMNS ( { currDate }, "dt", [Value] + 0 )
VAR dates1 =
NATURALINNERJOIN ( leftTbl1, rightTbl )
VAR dates2 =
NATURALINNERJOIN ( leftTbl2, rightTbl )
VAR val1 =
COUNTROWS ( dates1 )
VAR val2 =
COUNTROWS ( dates2 )
VAR ternary =
SWITCH (
TRUE (),
val1 = 1
&& val2 = 1, val1 & "-unique entry",
val1 = 1
&& val2 <> 1, val1 & "-has duplicate entry",
"0" & "-has duplicate entry"
)
RETURN
ternary