Display only repeated number

[Anonymous]

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
RADOC0010063	RAD	Cancelled
RADOC0010076	RAD	Cancelled
RADOC0010079	RAD	Cancelled
RADOC0010079	RAD	Cancelled
RADOC0010079	RAD	Cancelled
RADOC0010090	RAD	Cancelled
RADOC0010111	RAD	Cancelled
RADOC0010141	RAD	Cancelled
RADOC0010144	RAD	Cancelled
RADOC0010144	RAD	Cancelled
RADOC0010179	RAD	Cancelled
RADOC0010211	RAD	Cancelled
RADOC0010211	RAD	Cancelled
RADOC0010211	RAD	Cancelled
RADOC0010281	RAD	Cancelled
RADOC0010282	RAD	Cancelled
RADOC0010284	RAD	Cancelled
RADOC0010284	RAD	Cancelled

Thanks !

[v-jayw-msft · Accepted solution]

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]

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

[Anonymous]

Hi @Anonymous 

You can refer to this link below

https://community.powerbi.com/t5/Desktop/Identify-duplicates-and-show-up-records/td-p/210061

https://community.powerbi.com/t5/Desktop/Only-Show-Duplicates/td-p/385062

Kudo me if this is the solution.

[Anonymous]

@Anonymous 

I am trying to refer the links but EARLIER function is not working.
please find image attached.

Could you please help me on this .

Thanks !

[Anonymous]

Count Emails = 
Var Emails = [Emails]
RETURN

CALCULATE(
    COUNTROWS('Your Table'),
    all('Your Table'),
    'Your Table'[Emails] = Emails
)

You can try this one. Available in the 1st link @Anonymous 

[amitchandak]

@Anonymous , You might have create a table

distinct(Table[Id])

[Anonymous]

@amitchandak ,

I want to show only repeated ID not the distnict ID.

[Anonymous]

Hey If my answer was helpful please mark it as a solution @Anonymous