cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Count no of comma occurrences in a column

I have a table like below 

 

servernames

--------------

server1, server2, server3

server1

server4, server5, server6

 

I want results as below using either a measure or custom column in powerBI desktop

 

servernames                                     no_of_servers

--------------                                    ----------------

server1, server2, server3                           3

server1                                                      1

server4, server5                                         2

 

I can easily do the above in tsql. How do I achieve the same in PowerBI ?

 

Appreciate your help.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Sage
Solution Sage

Re: Count no of comma occurrences in a column

DAX for calculated column:

 

Server Count = LEN(Servers[servernames])-LEN(SUBSTITUTE(Servers[servernames],",",""))+1

 

View solution in original post

9 REPLIES 9
Highlighted
Super User IV
Super User IV

Re: Count no of comma occurrences in a column

In general, you would do a split of that column in Power Query and then unpivot the resulting columns and then you could count them relatively easily in DAX. As it is, you will likely have much better luck counting them in "M" Power Query code than in DAX. 

 

For example, you could do a Text.Split to return a List of values and then just a List.Count to count the items in it. For example, if you had a column called "servernames", you should be able to create a custom column in Power Query like:

 

=List.Count(Text.Split([servernames],","))

---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Solution Sage
Solution Sage

Re: Count no of comma occurrences in a column

DAX for calculated column:

 

Server Count = LEN(Servers[servernames])-LEN(SUBSTITUTE(Servers[servernames],",",""))+1

 

View solution in original post

Highlighted
Super User IV
Super User IV

Re: Count no of comma occurrences in a column

Nifty!


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
New Member

Re: Count no of comma occurrences in a column

This thing does not work if the delimiter is a "/".

 

I tried this thing and even List.Count leads to 400 bad request

Highlighted
New Member

Re: Count no of comma occurrences in a column

This thing does not work List.Count always return 400 bad request and Text.Split does not work if delimiter is /

Highlighted
Anonymous
Not applicable

Re: Count no of comma occurrences in a column

@Steve_Wheeler

 

This works well but the column I am working on has null values and the count for them is showing as "1" as well. Is there a sightly different DAX expression where the count of the null values is zero? Any solution would be helpful

 

Thanks

Highlighted
Frequent Visitor

Re: Count no of comma occurrences in a column

CSPBI.PNG

 

Hello, For some reason i get a DAX error attempting to do this.

I have 3 items coming from A SharePoint list - GH, CGO, FUE

 

It can be any combination of the 3, and they are seperated by a Comma.

I wish to sum the amount the user selected in the multi choice.

 

e.g GH = 1 // GH,CGO = 2 // GH,CGO,FUE = 3.

 

many thanks, and sorry I'm a newbie!

 

Gary

Highlighted
Anonymous
Not applicable

Re: Count no of comma occurrences in a column

Server Count = LEN(Regional Audit Tracker[BL Auditing])-LEN(SUBSTITUTE(Regional Audit Tracker[BL Auditing]),",",""))+1

Try this 

Highlighted
Frequent Visitor

Re: Count no of comma occurrences in a column

Thanks,

 

i got it working with this: 

 

BLActualCount = LEN('Regional Audit Tracker'[BL Auditing])-LEN(SUBSTITUTE('Regional Audit Tracker'[BL Auditing],",",""))+1

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors