Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
DAX for calculated column:
Server Count = LEN(Servers[servernames])-LEN(SUBSTITUTE(Servers[servernames],",",""))+1
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.
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
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
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
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!
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],","))
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
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |