Repeated Values

[Carlo1975]

Hi I have a column with my sell ID.

ID
00065862
00035306
00049674
00038281
00036700
00053195
00032684
00078092
00049675
00078095
00048102
00048101
00048103
00038598
00035305
00035304
00035306
00043491
00036782
00082791
00039057
00032684
00048101

I have to insert a new column to locate the Repeated ID Value, but, after that, I have to exclude one of it (I will do it with a filter) and so, is possible to check just one of the two or more values repeated?

I attache an example

ID	repeated IDs
00065862
00049674
00049674	1
00038281
00032684
00053195
00032684	1

In my example repeating IDs are 00049674 and 00032684 but I have to check just one of each, because after I will filter out all 1. Is not important if I sign the first or the second of repeated value.

[edhans · Accepted solution]

You could use a calculated column like this @Carlo1975 

Instance Count = 
VAR varCurrentID = 'Table'[ID]
RETURN
COUNTROWS(
    FILTER(
        ALL('Table'),
        'Table'[ID] = varCurrentID
    )
)

Same as a COUNTIFS() in Excel.

[edhans]

I'm not clear on your requirements exactly @Carlo1975 , but you cannot just check a few items to answer that specific question. Power BI, either in Power Query or DAX, will check the entire dataset based on the formula you use.

[Carlo1975]

Ok, I understand, ans so, more simple. I have to add a column and check when a Id is repeated. Like if in exel i use CountIf

[AlB]

Hi @Carlo1975 

If you create a calulcated table with

     ALL(Table1[ID])

the duplicates will already be removed

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers