DAX Formula to Extract First 3 Characters

Objective

Create a new column in a table that contains the first three characters of the values from an existing column (Column B).

DAX Formula

To achieve this, you can use the following DAX formula:

NewColumn = LEFT(TableName[ColumnB], 3)

Explanation of the Formula

• LEFT Function: This function extracts a specified number of characters from the start of a text string.
  • Parameters:
    • TableName[ColumnB]: This specifies the column from which the three characters will be extracted. Replace TableName with the actual name of your table.
    • 3: This indicates that the function should take the first three characters.
• NewColumn: This represents the new column that will hold the resulting values.

Practical Example

Assume you have a table named Products with a column ProductCode, and you need to create a new column called ShortCode that contains the first three characters of ProductCode.

Implementation

1. Open your data model in Power BI Desktop.
2. Navigate to the Data View.
3. Select the Products table.
4. On the ribbon, click on Modeling and then select New Column.
5. Enter the following formula:

ShortCode = LEFT(Products[ProductCode], 3)

Outcome

• If the ProductCode in the first row is "ABC123", the value in the ShortCode column will be "ABC".
• This allows you to easily categorize or analyze the data based on the first three characters of the ProductCode.

Summary

This DAX formula enables the creation of a new column that efficiently extracts and displays the first three characters of the specified column. This technique can be applied in various scenarios where text manipulation is needed in data analysis. For further enhancement of DAX skills, the Enterprise DNA Platform offers valuable resources and courses.