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
vkomarag
Helper III
Helper III

Count of numbers in a text field

Hi All,

 

 I have a column with text and numbers. I need to count of only numbers in that field.

I am trying to use FILTER but it's throwing error that it cannot support comparing values of type TEXT with values of type INTEGER.

Is there any way that i can do this?

 

Example:

 

Column1:

A

B

1

2

3

4

5

 

Here, I need to count only where column1=2

 

Thanks

KVB

1 ACCEPTED SOLUTION

@vkomarag

 

My answer was for this Question:

 

have a column with text and numbers. I need to count of only numbers in that field.

 

Now, if you need to count only the rows with value 2.

 

Do this:

 

MyMeasure=Countrows(Filter(Table,Table[Column1]="2"))




Lima - Peru

View solution in original post

11 REPLIES 11
Vvelarde
Community Champion
Community Champion

@vkomarag

 

Hi,

 You can use the filter using column1="2"




Lima - Peru
Anonymous
Not applicable

Count all of the values that are numbers:

CountFunction = Calculate(
	count('Table'[Column1]),
	isNumber('Table'[Column1])
)

 

Count all of the values that equal 2:

CountFunction = Calculate(
	count('Table'[Column1]),
	'Table'[Column1] = 2
)

I got blank values for the below formula

 

CountFunction = Calculate(
	count('Table'[Column1]),
	isNumber('Table'[Column1])
)

@Anonymous

 

The measures only works if column1 are Number Type.

 

 




Lima - Peru
Anonymous
Not applicable

Ah rats. Good point.

@vkomarag

 

hi, The first try with:

 

CountFunction =
CALCULATE (
    COUNT ( 'Table2'[Column1] ),
    NOT ( ISERROR ( VALUE ( 'Table2'[Column1] ) ) )
)



Lima - Peru

Capture.PNG

 

I got the same error.

@vkomarag

 

My answer was for this Question:

 

have a column with text and numbers. I need to count of only numbers in that field.

 

Now, if you need to count only the rows with value 2.

 

Do this:

 

MyMeasure=Countrows(Filter(Table,Table[Column1]="2"))




Lima - Peru

Yes. It worked. Thank you.

Anonymous
Not applicable

These nice folks are trying to help, but I'm going with... "seriously?".   Feels like you should redesign your table/model, cuz that is just WEIRD.

VARCHAR datatype and and internal conversions are common everywhere. Because of Power BI cannot process my data , I can't change my model.

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.