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