Count rows in a column that contain a string of 3,3. (3 Text Char COMMA 3 Text Char)

[EWBI]

I have a column of names that have names typed in by last name separated by a comma and a first name.

I want to find rows in that column that contain only partial names using 3 characters for the first name separated by a comma and 3 characters of the last name.  So in short Im looking for a measure or some process of making a measure for counting rows that contain 3,3 name format. Including the comma. 

Please help.

Sincerely Ed W.

[v-yaningy-msft · Accepted solution]

Hi, @EWBI 

Can I ask if you have currently resolved this issue? You can refer to @AnalyticsWizard  @tamerj1  @Dangar332  @lbendlin reply, if it doesn't work, you can refer to my method below.

FilterName = 
VAR _char = { ";" }
VAR _lengths =
    LEN ( SELECTEDVALUE ( 'Table'[Named Entered] ) )
VAR _searchchar =
    SEARCH ( _char, SELECTEDVALUE ( 'Table'[Named Entered] ), 4, 0 )
VAR _result =
    IF ( _lengths = 7 && _searchchar = 4, 1, 0 )
RETURN
    _result

Counts FilterName = 
CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', [FilterName] = 1 ) )

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[EWBI]

THIS WORKED!!!! Thank you so much!!!!!

I am trying to self teach myself power bi so far through youtube, google and help communities. I made

a few decent report visualizations but I am still very elementary in my knowledge. 

Can you recommend a better path of learning?

[AnalyticsWizard]

@EWBI 

You can create a measure in Power BI using the CALCULATE and CONTAINSSTRING functions to count the r...1234. Here’s an example of how you might write this measure:

CountOfNames = 
CALCULATE(
    COUNTROWS(Table1),
    FILTER(
        Table1,
        LEN(Table1[Name]) - LEN(SUBSTITUTE(Table1[Name], ",", "")) = 1 && -- Check if there is only one comma
        LEN(TRIM(MID(Table1[Name], 1, FIND(",", Table1[Name], 1, 0) - 1))) = 3 && -- Check if the first name has 3 characters
        LEN(TRIM(MID(Table1[Name], FIND(",", Table1[Name], 1, 0) + 1, LEN(Table1[Name])))) = 3 -- Check if the last name has 3 characters
    )
)

In this measure, Table1 is the name of your table and Name is the name of your column. This measure counts the number of rows in Table1 where the Name column contains exactly one comma and both the first name and last name are exactly 3 characters long.

Please replace Table1 and Name with your actual table name and column name. If you need further assistance or have more specific requirements, feel free to ask! 😊

[EWBI]

This worked too! Thank you so much for your help! I tried one of the suggestions above yours first and that worked as well. I appreciate the explanation on what I needed to do in regards to replacing the Table1 and name with the actual name. As someone who is clueless as I am this is extremely helpful. 🙂

[tamerj1]

Hi @EWBI 
In addtion to the following proposed solution you can have two parameters to select the required string length and the number of entered names.

Count = 
SUMX ( 
    VALUES ( 'Table'[Named Entered] ),
    VAR String = 'Table'[Named Entered]
    VAR Items = SUBSTITUTE ( String, ";", "|" )
    VAR Length = PATHLENGTH ( Items )
    VAR T1 = GENERATESERIES ( 1, Length, 1 )
    VAR T2 = FILTER ( T1, LEN ( PATHITEM ( Items, [Value] ) ) = 3 ) -- 3 can be be replaced with a parameter (string length)
    RETURN
        INT ( Length = COUNTROWS ( T2 ) && Length = 2 ) -- 2 can be replaced with a parameter (number of Names Entered)
)

[EWBI]

Thank you for your time and effort. I really appreciate it.  

I did try this but I could not get it to work. I tried entering it verbatim entering my table name. However when I type in " Length" thats when it all starts to fall apart. It gets that red squigly line underneath as if it thinks its wrong. Then T1 and T2 as well. What is T1 and T2?
I did find a solution in one of the proposed solutions above though. 

Again I truly appreciate your willingness to help. Please continue to be a hero to newbies like me.

[tamerj1]

@EWBI 

That's ok. It happens all the time. It must be some syntax error. If you're still interested you may paste a screenshot of your dax in order to debug it. 

[EWBI]

I appreciate your help. I am not very good at power bi but I am learning. Please forgive me but I attempted to add a calculated column and this is what I am getting. (BTW I corrected the column name from "Named Entered" to "Name Entered")

[Dangar332]

Hi, @EWBI 

Code given by @lbendlin  is calculated column code not for measure
if you want it for measure try below

Measure = 
if(
 CONTAINSSTRING(MIN('Table'[Named Entered]),"???;???")&& LEN(MIN('Table'[Named Entered]))=7,
 1,
 0
)

[EWBI]

Thank you so much for your time and help. I really appreciate it. 

Although the measure didn't work I'm sure due to my lack of knowledge of how to input.  i did find a solution in one of the other posts.

[lbendlin]

CONTAINSSTRING([Column],"???,???") && LEN([Column])=7

You can get cuter than that of course to test each position for {"A".."Z"}

[EWBI]

I can't seem to use this with just the column name. It wants a measured column. I dont know how to convert my text columns into a measure. Any advice?

[lbendlin]

there should not be a need for a measure.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[EWBI]

below is a sample of data. The user name row has partial names in a 3;3 character entry. I am trying to find a way to make a column that shows the count of 3;3 found per row in the Name Entered column.

Location	Department	User	User Grouping Two	User Roles	Event Time	Event Name	Number of fields entered in search	Named Entered	DOB Entered	Sex Entered	SSN Entered
Earth	DEPT 1	Sample,User		Role	12/31/2023   3:07:33PM	Name Entry	2	Sam;use	12/18/1983
Earth	DEPT 2	Sample,User		Role	12/31/2023   3:15:26PM	Name Entry	2	Sample;User	3/10/1975
Earth	DEPT 3	Sample,User		Role	12/31/2023   3:25:09PM	Name Entry	2	Fak;Per	7/19/1991
Earth	DEPT 4	Sample,User		Role	12/31/2023   3:28:22PM	Name Entry	2	Fake;Person	8/12/1990
Earth	DEPT 5	Sample,User		Role	12/31/2023   3:35:39PM	Name Entry	2	LastName;FirstName	10/16/1969
Earth	DEPT 6	Sample,User		Role	12/31/2023   3:39:09PM	Name Entry	2	Las;Nam	6/6/1980

[EWBI]

Im sorry part of my reply is cut off for some reason but you should get the gist.

The user name row has partial names in a 3;3 character entry. I am trying to find a way to make a column that shows the count of 3;3 found per row in the Name Entered column.

[lbendlin]

I changed your sample data a little to cover more scenarios.  Flag is a calculated column.