DAX formula to calculate count of occurances for the items in a list

[DJ]

Hi All,

Is this possible to calculate that how many times a value is repeating?

For Example, lets assume I have some values in a column-A and it has duplicates as well. I want count of repeats in column-B like shown below:

Column1   Column2
A 8
A 8
A 8
A 8
B 4
B 4
C 5
C 5
C 5
C 5
B 4
B 4
A 8
A 8
A 8
A 8
C 5

This is too easy in excel but in DAX I am facing challenge to do so.

Thanks,
DJ

[ricardocamargos]

Hi @DJ,

You can try it by creating measure with this definition:

COUNT(Column1)

After it, drag a table visual, both columns and this new measure.

Also, you u wanna show all the line, you have to add an index column (u can add it using Power Query). Just drag to the table and don't summarize it.

[Zubair_Muhammad]

@DJ

As a calculated column,,you could use

Column2 =
CALCULATE ( COUNTROWS ( Table1 ), ALLEXCEPT ( Table1, Table1[Column1] ) )

[DJ · Accepted solution]

Thanks Zubair, It worked exactly the way I wanted

[DJ]

Hi Zubair,

Could you please help me with the logic? Is there any itteration in this formula? I shared only one column and in the formula you removed that using AllExcept expression so how it worked?

I have just started learning DAX so it is really hard for me to understand many things.

Thanks,

DJ