DAX Formula for Extracting and Matching Characters

DAX Formula for Matching First Two Characters

Objective

To create a DAX formula that extracts the first two characters from a column in Table One and matches them against a column in Table Two.

DAX Formula

The following DAX formula performs the desired operation:

MatchedCount = 
COUNTROWS(
    FILTER(
        TableTwo,
        LEFT(TableOne[ColumnName], 2) = LEFT(TableTwo[MatchColumnName], 2)
    )
)

Explanation of the Formula

1. Functionality:

   • The formula counts the number of rows in TableTwo where the first two characters of a specified column in TableOne match the first two characters of a specified column in TableTwo.

2. Detailed Components:

   • LEFT(TableOne[ColumnName], 2): This function retrieves the first two characters from ColumnName of TableOne.
   • LEFT(TableTwo[MatchColumnName], 2): This function retrieves the first two characters from MatchColumnName of TableTwo.
   • FILTER(TableTwo, ...): This evaluates each row in TableTwo to check if the extracted first two characters from both tables are equal.
   • COUNTROWS(...): This counts all rows that meet the condition specified in the FILTER function.

Practical Illustration

Scenario Example

• Assume you have two tables:
  • TableOne with a column ID containing values such as AB123, CD456.
  • TableTwo with a column Reference containing values such as ABxx, CDyy, EFzz.

Using the Formula

If the formula is implemented in a calculated column or measure within Power BI, it will evaluate:

• For AB123: It will match against ABxx in TableTwo.
• For CD456: It will match against CDyy in TableTwo.

Given that both ABxx and CDyy are found in TableTwo, the result of the measure would be 2, indicating that there are two matching entries.

Conclusion

This DAX formula is an effective solution for matching the first two characters of entries in Table One against entries in Table Two. It provides flexibility for counting and evaluating conditions across different tables in a Power BI model, thus enhancing data insights.

For further exploration of DAX concepts and complex modeling techniques, consider courses available on the Enterprise DNA Platform.