Forum Discussion

superjam's avatar
superjam
Helper II
3 years ago

Count repeated values on three columns

Hello:

 

I have a "Table" with three columns, A, B and C, filled with many different string values.

I want to find how many rows of that table have all three columns equal to another row in the table. In other words, find rows with duplicate "A+B+C". In a visual I would need a number, indicating how many non unique values there are, combining the three columns.

Is there a formula to achieve this or do I have to concatenate all three columns in one first and then somehow count the duplicates?

 

Thank you very much and regards.

DAX
Duplicates

5 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi superjam 
    In general that depends on the filter context which essentially depens on the visual itself. However, The following should work in a card visual

    Count =
COUNTROWS (
    DISTINCT ( SELECTCOLUMNS ( "A", 'Table'[A], "B", 'Table'[B], "C", 'Table'[C] ) )
)
    • superjam's avatar
      superjam
      Helper II
      3 years ago

      Thank you, FreemanZ 

       

      The function DISTINCT() counts unique values in one column of the table. So, your measure gives the repeated values in one column.

       

      But how about the following example:

      Only one row is duplicated (marked in yellow), because only in that case all three columns are equal with each other. I hope this clarifies the problem.

       

      Thanks again. 

  • superjam's avatar
    superjam
    Helper II
    3 years ago

    Hello,

     

    Just after posting the last response, I figured it out: create a new column concatenating the three columns and then use the idea of FreemanZ to calculate the duplicate ones.