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
Cezenwoko
New Member

Count the amount of times a value appears in a column

Hello,

 

I need to calculate the amount of time each value appears in a particular column. The issue I'm having is that some rows have multiple values. Here's an example:

 

Column 1

A

B
A
B
D
C
D
B
E
F

 

I'd like to use a dax that will calculate each time the value appears. So the count would show A =2, B =3, D =2

 

Thank you!

1 ACCEPTED SOLUTION

Hi, @Cezenwoko 

Enter a new table 'Table2' as below:

veasonfmsft_0-1657620742814.png

Then try measure as below:

Result = CALCULATE(COUNTROWS('Table1'),CONTAINSSTRING('Table1'[Column 1],MAX(Table2[Column1])))

veasonfmsft_1-1657620819167.png

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Keisha333
New Member

I am wanting to have a colunm that counts the rows. 
This is my data but the ID does not count from 1, 2 ,3 and so on so thought I would add a colum to have the correct count per line, hope that makes sense

Keisha333_0-1721355697961.png

 

James-Harpin
Resolver IV
Resolver IV

Hello @Cezenwoko 

 

Try creating a calculated column using the following DAX formula: 

Totals = CALCULATE( COUNT('Table'[Column1]), ALLEXCEPT('Table', 'Table'[Column1]))

you're a dawg for this one thanks for bailing me out. This should be the real accepted solution

thank you very much for the solution 🙂 

Hi @James-Harpin 

 

Thank you for your response!

 

I initially tried this DAX but the results come out as: A = 1, B =1, ABD = 1. As opposed to counting each value individually.

Hi, @Cezenwoko 

Enter a new table 'Table2' as below:

veasonfmsft_0-1657620742814.png

Then try measure as below:

Result = CALCULATE(COUNTROWS('Table1'),CONTAINSSTRING('Table1'[Column 1],MAX(Table2[Column1])))

veasonfmsft_1-1657620819167.png

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you!

 

I was looking for a way to create this without making another table but it seems like that's impossible right now

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.