Daily DAX : Day 195 ISLOGICAL

 The ISLOGICAL function in Power BI DAX (Data Analysis Expressions) checks whether a value is a logical value (TRUE or FALSE). It returns TRUE if the value is a logical value and FALSE otherwise.

Syntax

ISLOGICAL(value)

    value: The expression or value to test.

Return Value

    TRUE: If the value is a logical value (TRUE or FALSE).

    FALSE: If the value is not a logical value (e.g., text, number, date, etc.).

Use Case

ISLOGICAL is useful in scenarios where you need to validate or filter data based on whether a value is a boolean (logical) type. It’s often used in conditional logic, data cleansing, or debugging calculated columns/measures.

Example Scenario

Suppose you have a table with a column Flag that contains mixed data types (logical values like TRUE/FALSE, text, numbers, etc.), and you want to identify rows where the Flag column contains logical values.

Sample Data:

ID            Flag

1              TRUE

2              FALSE

3              "Yes"

4              100

5              TRUE

DAX Formula:

Create a calculated column to check if the Flag column contains logical values:

IsFlagLogical = ISLOGICAL('Table'[Flag])

Result:

ID            Flag            IsFlagLogical

1              TRUE            TRUE

2              FALSE           TRUE

3               "Yes"             FALSE

4                100               FALSE

5                TRUE           TRUE

Practical Use Cases

    Data Validation: Use ISLOGICAL to ensure a column intended for boolean values (e.g., flags like IsActive, IsComplete) contains only TRUE/FALSE values.

    Conditional Formatting: Filter or highlight rows in reports where a column unexpectedly contains non-logical values.

    Debugging Measures: When writing complex DAX measures, ISLOGICAL can help verify if an expression evaluates to a logical value as expected.

    Dynamic Filtering: Combine with other DAX functions to create dynamic measures that behave differently based on whether a value is logical.

Example in a Measure

To count rows where the Flag column contains logical values:

CountLogicalFlags = 

CALCULATE(

    COUNTROWS('Table'),

    ISLOGICAL('Table'[Flag]) = TRUE

)

This measure returns the number of rows where Flag is TRUE or FALSE (in the example table, it would return 3).

Notes

    ISLOGICAL is a simple type-checking function and does not convert values to logical types.

    It’s often used with other DAX functions like IF, FILTER, or CALCULATE for more complex logic.

    Be cautious when dealing with blank or null values, as ISLOGICAL(BLANK()) returns FALSE.

For more details, you can refer to Microsoft’s DAX documentation: ISLOGICAL function.