Forum Discussion
Duplicate value
Hello experts,
How to find repetitious value in the column name and then enter them with their path to a new table?
Hi koorosh, the easiest way is to add "calculated column" to your table to count current name occurences:
CountName = VAR _NameOnACurrentRow = 'Table'[Name] //Name at currently iterating row RETURN COUNTROWS( //needs to aggregate the table (i.e. find number of rows) FILTER( 'Table', //all table "Table" [Name] = _NameOnACurrentRow //filtered with a value of currently iterating row ) )
This will give you the following table:
Now, when creating a new table the only thing you need to do is to filter this table:
Here is DAX code:Repetitive values = FILTER( 'Table', [CountName] > 1 )If you prefer you can avoid creation of calculated column by combining steps in new table definition
Repetitive values = VAR _TableWithCount = ADDCOLUMNS( 'Table', "@CountName", VAR _CurrentName = 'Table'[Name] RETURN COUNTROWS( FILTER( 'Table', [Name] = _CurrentName ) ) ) RETURN FILTER( _TableWithCount, [@CountName] > 1 )
Good luck with your work! 🙂
3 Replies
- Sergii24Super User
Hi koorosh, the easiest way is to add "calculated column" to your table to count current name occurences:
CountName = VAR _NameOnACurrentRow = 'Table'[Name] //Name at currently iterating row RETURN COUNTROWS( //needs to aggregate the table (i.e. find number of rows) FILTER( 'Table', //all table "Table" [Name] = _NameOnACurrentRow //filtered with a value of currently iterating row ) )
This will give you the following table:
Now, when creating a new table the only thing you need to do is to filter this table:
Here is DAX code:Repetitive values = FILTER( 'Table', [CountName] > 1 )If you prefer you can avoid creation of calculated column by combining steps in new table definition
Repetitive values = VAR _TableWithCount = ADDCOLUMNS( 'Table', "@CountName", VAR _CurrentName = 'Table'[Name] RETURN COUNTROWS( FILTER( 'Table', [Name] = _CurrentName ) ) ) RETURN FILTER( _TableWithCount, [@CountName] > 1 )
Good luck with your work! 🙂