Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Display only repeated number

Hi, I want to create a column which ID's are repeted from column ID.   Id Type Status RADOC0010024 RAD Cancelled RADOC0010024 RAD Cancelled RADOC0010056 RAD Cancelled RADOC0...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    Create a calculated column on original table.

    Column = CALCULATE(COUNT('Table'[Id]),ALLEXCEPT('Table','Table'[Id]))

     

    Then create a new table and filter out the records which count <=1.

    Table 2 = DISTINCT(SELECTCOLUMNS(FILTER('Table','Table'[Column]>1),"id",'Table'[Id]))

     

    Best Regards,

    Jay

PhilipTreacy's avatar
PhilipTreacy
Icon for Super User rankSuper User
5 years ago

Hi Anonymous 

Download this PBIX file with the following solutions

This works to create a Calculated Column

Count Dup = 
Var ids = [Id]
RETURN

CALCULATE(
    COUNTROWS('Table'),
    ALL('Table'),
    'Table'[Id] = ids
)

 

or you can do it in Power Query.  Group By ID and then choose Count as the aggregation.

With both methods just filter out the count's less than 2.

Regards

Phil