DAX中统计首字符为英文字母的值的数量

问题描述 #

现有如下数据：

129302
;;;;
A.T.P.
Hi@gmail.com
Peter
'-

?
Hi

需要统计其中**首字符为英文字母（a-z/A-Z）**的值的数量，预期结果为4。已知统计首字符为'M'的DAX代码如下：

Count = 
COUNTROWS ( 
    FILTER ( 
        'Table' , 
        LEFT ( 'Table'[column B] , 1 ) = "M" )
    )
)

请问如何修改上述代码，使其能统计首字符为任意英文字母的值？

修改后的DAX代码 #

方法一：枚举所有英文字母 #

Count = 
COUNTROWS (
    FILTER (
        'Table',
        LEFT ( 'Table'[column B], 1 ) IN {"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z"}
    )
)

方法二：利用范围判断（更简洁） #

Count = 
COUNTROWS (
    FILTER (
        'Table',
        UPPER ( LEFT ( 'Table'[column B], 1 ) ) >= "A" && UPPER ( LEFT ( 'Table'[column B], 1 ) ) <= "Z"
    )
)

代码说明 #

• 方法一通过IN运算符，直接判断首字符是否属于包含所有大小写英文字母的集合，逻辑直观。
• 方法二先用UPPER函数将首字符统一转换为大写，再判断该字符是否处于"A"到"Z"的区间内，无需枚举所有字母，代码更简洁高效。

内容的提问来源于stack exchange，提问作者EvitaSchaap