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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TheRockStarDBA
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
Anonymous
Not applicable

DAX for calculated column:

 

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

 

View solution in original post

11 REPLIES 11
dwightarobinson
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)
Anonymous
Not applicable

DAX for calculated column:

 

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

 

I realize this thread is old; however, the solution is working for me EXCEPT, I need to count the number of "\\".  When I use the below, it counts each "\" as one, but the double "\\" is the delimeter in my text. Help!

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

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

Anonymous
Not applicable

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

Try this 

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

@Anonymous

 

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

Nifty!


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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