Forum Discussion

koorosh's avatar
koorosh
Post Partisan
2 years ago
Solved

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

  • 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! 🙂

    • koorosh's avatar
      koorosh
      Post Partisan

      Hi Sergii,

      The first calculated column make the following result:

      And the create table make the following:

      But now we have a table with duplicated rows. We need just one row for John and one row for Mike???