Daily DAX : Day 207 ISNONTEXT

 The ISNONTEXT function in Power BI DAX (Data Analysis Expressions) checks whether a given value is not a text value. It returns TRUE if the value is not text (e.g., a number, date, boolean, or blank) and FALSE if the value is text.

Syntax

dax

ISNONTEXT(value)

    value: The expression or column value to evaluate.

Return Value

    TRUE: If the value is not a text string.

    FALSE: If the value is a text string.

How It Works

    ISNONTEXT is useful for data validation or conditional logic in DAX calculations, especially when you need to differentiate text from non-text values.

    It evaluates a single value or a column and checks its data type.

    Blank values are considered non-text, so ISNONTEXT(BLANK()) returns TRUE.

Use Cases

    Data Cleansing/Validation:

        Identify non-text values in a column that is expected to contain text, such as checking for invalid entries in a "Name" or "Description" field.

        Example: Flag rows where a column contains numbers or blanks instead of text.

    Conditional Calculations:

        Perform calculations only on non-text values, such as excluding text entries when calculating averages or sums.

        Example: Skip text values in a column when aggregating data.

    Error Handling:

        Prevent errors in formulas by checking if a value is non-text before applying functions that expect numbers or dates.

Example

Suppose you have a table SalesData with a column ProductCode that should contain text but sometimes contains numbers or blanks due to data entry issues.

DAX Formula

dax

NonTextCheck = IF(ISNONTEXT(SalesData[ProductCode]), "Invalid", "Valid")

Explanation

    For each row in SalesData, the formula checks if ProductCode is non-text.

    If ISNONTEXT returns TRUE (e.g., the value is a number or blank), it labels the row as "Invalid."

    If ISNONTEXT returns FALSE (the value is text), it labels the row as "Valid."

Sample Data

ProductCode                NonTextCheck

ABC123                            Valid

12345                                Invalid

(Blank)                              Invalid

XYZ789                            Valid

Practical Scenario

Scenario: A company imports sales data, but the CustomerID column sometimes contains numbers or blanks instead of text-based IDs. You want to create a measure to count invalid CustomerID entries.

DAX Measure

dax

InvalidCustomerIDs = 

CALCULATE(

    COUNTROWS(SalesData),

    ISNONTEXT(SalesData[CustomerID])

)

    This measure counts rows where CustomerID is not text, helping identify data quality issues.

Notes

    ISNONTEXT is often used alongside other DAX functions like IF, CALCULATE, or FILTER for conditional logic.

    It complements ISTEXT, which checks if a value is text.

    Be cautious with blanks, as they are treated as non-text, which might affect your logic depending on your requirements.

Related Functions

    ISTEXT: Checks if a value is text.

    ISNUMBER: Checks if a value is a number.

    ISBLANK: Checks if a value is blank.

By using ISNONTEXT, you can enhance data quality checks and ensure robust DAX calculations in Power BI.