Forum Discussion

Carlo1975's avatar
Carlo1975
Helper I
5 years ago
Solved

Repeated Values

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

 

IDrepeated IDs
00065862 
00049674 
000496741
00038281 
00032684 
00053195 
000326841

 

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.

Need Help
  • edhans's avatar
    edhans
    5 years ago

    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.

     

4 Replies

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    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 

     

  • edhans's avatar
    edhans
    Community Champion
    5 years ago

    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's avatar
      Carlo1975
      Helper I
      5 years ago

      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

  • edhans's avatar
    edhans
    Community Champion
    5 years ago

    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.