cancel
Showing results for
Search instead for
Did you mean:
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
Solution Sage

DAX for calculated column:

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

10 REPLIES 10
Regular Visitor

A good idea for the code, but in my case, I had to add a condition for when there is a blank description as seen below.

Word Count =
IF(AND(
(LEN(GL[EntryDescription])=LEN(SUBSTITUTE(GL[EntryDescription]," ",""))),LEN(GL[EntryDescription])<1),0,
IF(
(LEN(GL[EntryDescription])=LEN(SUBSTITUTE(GL[EntryDescription]," ",""))),1,
LEN(GL[EntryDescription])-LEN(SUBSTITUTE(GL[EntryDescription]," ",""))+1)
Solution Sage

DAX for calculated column:

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

Frequent Visitor

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

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

Try this

Frequent Visitor

Thanks,

i got it working with this:

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

Anonymous
Not applicable

@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

Super User IV

Nifty!

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

@ me in replies or I'll lose your thread!!!

##### 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!

Super User IV

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],","))`

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

@ me in replies or I'll lose your thread!!!

##### 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!

New Member

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

New Member

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

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

## Helpful resources

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Check it out!

Click here to read more about the July 2021 Updates

#### Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

#### Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors