DAX Formula to Match First Two Characters Between Two Tables

Objective

The objective is to create a DAX formula that retrieves rows from Table Two where the first two characters of a specified field in Table One match entries in a specific column of Table Two.

DAX Formula

Below is the DAX formula to achieve this:

MatchingRows = 
FILTER(
    TableTwo,
    LEFT(TableOne[Field], 2) = TableTwo[Column]
)

Explanation of the Formula

1. FILTER Function:

   • This function is used to return a table that contains a subset of the original table (TableTwo) that meets a specified condition.

2. LEFT Function:

   • This function extracts the first two characters from the string in the specified column of Table One (TableOne[Field]).

3. Condition:

   • The formula checks if the resulting substring (the first two characters from TableOne[Field]) is equal to the values in TableTwo[Column].
   • This comparison filters the rows in Table Two based on whether the extracted characters match the values in the target column.

Practical Example

Tables Structure

Assume the following structures:

• Table One

  • Field: ProductCode
    • Example values: AB123, XY456, AB789

• Table Two

  • Column: CodePrefix
    • Example values: AB, XY, CD

Implementation Steps

1. Define Tables:

   • TableOne contains product codes.
   • TableTwo contains code prefixes that we want to match against.

2. Create DAX Measure:

   • Use the above DAX formula to create a measure in your data model. This measure will dynamically filter Table Two based on the matching condition.

3. Result:

   • When applied, the measure will yield:
     • For TableOne's values like AB123, the first two characters AB will match with TableTwo, thus filtering it down to rows that include AB in CodePrefix, resulting in relevant data extraction.

Conclusion

This DAX formula effectively enables users to filter data in Table Two based on the first two characters of a specified field in Table One. This capability enhances data analysis by allowing for dynamic matching between different datasets in the model. For further learning and practical applications of DAX, consider exploring the various courses available on the Enterprise DNA Platform.