topic Count repeated values on three columns in DAX Commands and Tips

Count repeated values on three columns

superjam — Tue, 07 Mar 2023 19:37:27 GMT

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.

Re: Count repeated values on three columns

FreemanZ — Tue, 07 Mar 2023 23:25:58 GMT

hi superjam

try like:

Measure =

COUNTROWS(TableName)-

COUNTROWS(DISTINCT(TableName))

Re: Count repeated values on three columns

superjam — Wed, 08 Mar 2023 12:16:27 GMT

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.

Re: Count repeated values on three columns

superjam — Wed, 08 Mar 2023 12:23:21 GMT

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.

Re: Count repeated values on three columns

FreemanZ — Wed, 08 Mar 2023 12:39:00 GMT

hi superjam

DISTINCT also accept table as argument.

Re: Count repeated values on three columns

tamerj1 — Wed, 08 Mar 2023 12:42:32 GMT

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] ) ) )