DAX Formula Development

Requirement Understanding

The objective is to create a DAX formula that checks if a given value:

1. Starts with the word "AOP"
2. Contains the word "ideate"

If both conditions are met, the formula should return the string "Company."

DAX Formula

The following formula can be used to achieve the specified conditions:

Result = 
IF(
    LEFT([YourColumnName], 3) = "AOP" && 
    SEARCH("ideate", [YourColumnName], 1, 0) > 0,
    "Company",
    BLANK()
)

Key Components of the Formula

• IF Function: This function evaluates a condition and returns one value if the condition is TRUE and another if FALSE.
• LEFT Function: This extracts a specified number of characters from the start of a string. Here, LEFT([YourColumnName], 3) checks the first three characters.
• SEARCH Function: This checks if the substring "ideate" exists within the string. It returns the position of the first occurrence; if not found, it returns an error which is handled by specifying a third argument 0 to return a value when not found.
• BLANK Function: This returns a blank result when conditions are not met.

Implementation Example

Sample Data

Assuming a table named Projects with a column called ProjectName, which might contain various project descriptions.

Applying the Formula

Add the above DAX formula as a calculated column in the Projects table:

Result = 
IF(
    LEFT(Projects[ProjectName], 3) = "AOP" && 
    SEARCH("ideate", Projects[ProjectName], 1, 0) > 0,
    "Company",
    BLANK()
)

Outcome

After applying the formula, the resulting table may appear as follows:

Conclusion

The above DAX formula accurately checks whether a project name:

• Starts with "AOP"
• Contains the word "ideate"

If both conditions are satisfied, it returns "Company," otherwise, it returns a blank. This implementation effectively streamlines project categorizations based on naming conventions. For further enhancement of DAX skills, consider exploring the Enterprise DNA Platform.