了解文本函数

DAX 中几乎所有可用的文本函数都与 Excel 类似, 只有少数例外：它们是 CONCATENATE, EXACT,FIND, FIXED, FORMAT, LEFT, LEN, LOWER, MID, REPLACE, REPT, RIGHT, SEARCH, SUBSTITUTE, TRIM, UPPER 和 VALUE. 这些函数对于操作文本和从包含多个值的字符串中提取数据非常有用。

实例

例如，下图中，你可以看到一个从字符串中提取名和姓的示例，该字符串包含用逗号分隔的值，中间是标题，我们希望将其删除。

使用文本函数提取姓名的示例

我们从计算两个逗号的位置开始，然后使用代表位置的数字提取文本的目标部分。SimpleConversion 列使用的公式在字符串少于两个逗号时可能会返回错误值 (如果压根没有逗号，则会引发错误)，而 FirstLastName 列使用了一个更复杂的表达式，不会在缺少逗号的情况下失败：

Comma1 = IFERROR ( FIND ( ",", People[Name] ), BLANK ( ) )

Comma2 = IFERROR ( FIND ( ",", People[Name], People[Comma1] +1 ), BLANK ( ) )

SimpleConversion =
MID (
    People[Name],
    People[Comma2] + 1,
    LEN ( People[Name] )
) & " "
    & LEFT (
        People[Name],
        People[Comma1] - 1
    )

FirstLastName =
TRIM (
    MID (
        People[Name],
        IF (
            ISNUMBER ( People[Comma2] ),
            People[Comma2],
            People[Comma1]
        ) + 1,
        LEN ( People[Name] )
    )
)
    & IF (
        ISNUMBER ( People[Comma1] ),
        " "
            & LEFT (
                People[Name],
                People[Comma1] - 1
            ),
        ""
    )

Comma1 = IFERROR ( FIND ( ",", People[Name] ), BLANK ( ) )
Comma2 = IFERROR ( FIND ( ",", People[Name], People[Comma1] +1 ), BLANK ( ) )
SimpleConversion =
MID (
People[Name],
People[Comma2] + 1,
LEN ( People[Name] )
) & " "
& LEFT (
People[Name],
People[Comma1] - 1
)
FirstLastName =
TRIM (
MID (
People[Name],
IF (
ISNUMBER ( People[Comma2] ),
People[Comma2],
People[Comma1]
) + 1,
LEN ( People[Name] )
)
)
& IF (
ISNUMBER ( People[Comma1] ),
" "
& LEFT (
People[Name],
People[Comma1] - 1
),
""
)

Comma1 = IFERROR ( FIND ( ",", People[Name] ), BLANK ( ) )

Comma2 = IFERROR ( FIND ( ",", People[Name], People[Comma1] +1 ), BLANK ( ) )

SimpleConversion =
MID (
    People[Name],
    People[Comma2] + 1,
    LEN ( People[Name] )
) & " "
    & LEFT (
        People[Name],
        People[Comma1] - 1
    )

FirstLastName =
TRIM (
    MID (
        People[Name],
        IF (
            ISNUMBER ( People[Comma2] ),
            People[Comma2],
            People[Comma1]
        ) + 1,
        LEN ( People[Name] )
    )
)
    & IF (
        ISNUMBER ( People[Comma1] ),
        " "
            & LEFT (
                People[Name],
                People[Comma1] - 1
            ),
        ""
    )

如你所见，FirstLastName 列是由一个很长的 DAX 表达式定义的，但是你必须使用它来避免可能的错误，因为如果单个值产生错误，就会传递到整个列。

虽然 DAX 具备提取从文本中提取数据的能力，但建议你尽量在编辑查询阶段或数据库中提前完成类似操作，避免在模型中进行数据清洗。