Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
DJ
Regular Visitor

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

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

1 ACCEPTED SOLUTION

Thanks Zubair, It worked exactly the way I wanted Smiley Happy

View solution in original post

4 REPLIES 4
ricardocamargos
Continued Contributor
Continued Contributor

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.

 

 

 

@DJ

 

As a calculated column,,you could use

 

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

Regards
Zubair

Please try my custom visuals

Thanks Zubair, It worked exactly the way I wanted Smiley Happy

DJ
Regular Visitor

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.