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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
EWBI
Helper I
Helper I

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

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.

1 ACCEPTED SOLUTION
v-yaningy-msft
Community Support
Community Support

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.

vyaningymsft_0-1712567304044.png

 

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


 

View solution in original post

16 REPLIES 16
v-yaningy-msft
Community Support
Community Support

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.

vyaningymsft_0-1712567304044.png

 

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


 

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
Super User
Super User

@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! 😊

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
Super User
Super User

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.
1.png

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)
)

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.

@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
Helper I
Helper I

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")

EWBI_0-1712456926219.png

 

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
)

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
Super User
Super User

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

 

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

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?

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...

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.

 

LocationDepartmentUserUser Grouping TwoUser RolesEvent TimeEvent NameNumber of fields entered in searchNamed EnteredDOB EnteredSex EnteredSSN Entered
EarthDEPT 1Sample,User Role12/31/2023   3:07:33PMName Entry2Sam;use12/18/1983  
EarthDEPT 2Sample,User Role12/31/2023   3:15:26PMName Entry2Sample;User3/10/1975  
EarthDEPT 3Sample,User Role12/31/2023   3:25:09PMName Entry2Fak;Per7/19/1991  
EarthDEPT 4Sample,User Role12/31/2023   3:28:22PMName Entry2Fake;Person8/12/1990  
EarthDEPT 5Sample,User Role12/31/2023   3:35:39PMName Entry2LastName;FirstName10/16/1969  
EarthDEPT 6Sample,User Role12/31/2023   3:39:09PMName Entry2Las;Nam6/6/1980  

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.

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

 

lbendlin_0-1712452653964.png

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.