Forum Discussion

zahidah_mabd's avatar
zahidah_mabd
Helper I
4 years ago
Solved

Count Rows With non-numerical value

Hi, I want to count the number of values that is non numerical for the position code.

The position code should be numerical values only
i.e: 000113 , 1000034, 222564

I want to count the rows that contains alphabets in the position code
i.e: A100034 , B000386

Sample data:

Position Code
02060123
10007788
A2226934
G1000045
0345892B


The value type for Position Code is text because we want to include "0" in codes that starts with zero (i.e: 02060123). If I use integer/numeric type then power bi will automatically removes the "0" (i.e: 2060123)



How can I count the position codes with non-numeric value?

Need Help
  • Samarth_18's avatar
    Samarth_18
    4 years ago

    Hi zahidah_mabd ,

     

    You could create a measure as below:-

    Measure =
CALCULATE (
    COUNT ( 'Table (2)'[Position Code] ),
    FILTER ( 'Table (2)', ISERROR ( INT ( 'Table (2)'[Position Code] ) ) )
)

     

    BR,

    Samarth

5 Replies

  • danextian's avatar
    danextian
    Super User
    4 years ago

    Hi zahidah_mabd 

     

    Create a column that will identify whether a row in a column is non-numeric

    IsNonNumeric =
ISERROR ( VALUE ( 'Table'[Column] ) )

    Use that column to count the rows that are non-numeric

    NonNumeric = 
CALCULATE ( COUNTROWS ( 'Table'), filter('Table', 'Table'[IsNonNumeric] = TRUE() ))

     

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

    Hi zahidah_mabd ,

     

    You could create a measure as below:-

    Measure =
CALCULATE (
    COUNT ( 'Table (2)'[Position Code] ),
    FILTER ( 'Table (2)', ISERROR ( INT ( 'Table (2)'[Position Code] ) ) )
)

     

    BR,

    Samarth

    • zahidah_mabd's avatar
      zahidah_mabd
      Helper I
      4 years ago
      Position Code
      02060123
      10007788
      A2226934
      G1000045
      0345892B

       


      The value type for Position Code is text because we want to include "0" in codes that starts with zero (i.e: 02060123). If I use integer/numeric type then power bi will automatically removes the "0" (i.e: 2060123)

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    Hi, zahidah_mabd ;

    You could add a column in power query , which could extract the alphabets from every rows:

    =Text.Select([Position Code],{"A".."a"})

     

    and count of this column. filter the not blank rows:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.