Daily DAX : Day 224 KEYWORDMATCH

 The KEYWORDMATCH function in Power BI's DAX (Data Analysis Expressions) language is used to identify whether a specified keyword appears within a text string and returns a score based on the match. It is particularly useful for text analysis, enabling users to detect the presence of specific words or phrases in a column of text data, such as customer feedback, survey responses, or product descriptions.

Syntax

KEYWORDMATCH(<text>, <keyword>, [<score_if_match>], [<score_if_no_match>])

    text: The text string or column containing the text to search.

    keyword: The word or phrase to search for within the text.

    score_if_match (optional): The value returned if the keyword is found. Defaults to 1.

    score_if_no_match (optional): The value returned if the keyword is not found. Defaults to 0.

How It Works

    KEYWORDMATCH performs a case-insensitive search for the specified keyword within the text.

    If the keyword is found, it returns the score_if_match value (default: 1).

    If the keyword is not found, it returns the score_if_no_match value (default: 0).

    The function is typically used in calculated columns or measures to flag or score rows based on the presence of specific terms.

Use Case

Scenario: A company wants to analyze customer reviews to identify mentions of "excellent service" to gauge positive feedback.

Example

Suppose you have a table Reviews with a column Comment containing customer feedback. You want to flag reviews that mention "excellent service."

    Create a Calculated Column:

    dax

    ExcellentServiceFlag = KEYWORDMATCH(Reviews[Comment], "excellent service", 1, 0)

        For each row in the Comment column, this checks if "excellent service" appears.

        Returns 1 if found, 0 if not.

    Sample Data:

    Comment                                                      ExcellentServiceFlag

    The team provided excellent service!                1

    Good product, but slow delivery                        0

    Excellent service and fast shipping                    1

    

Use in Analysis:

        Measure for Counting Matches:

        dax

        TotalExcellentMentions = SUM(Reviews[ExcellentServiceFlag])

        This calculates the total number of reviews mentioning "excellent service."

        Filter or Visualize: Use the ExcellentServiceFlag column in visuals (e.g., bar charts) to show the proportion of positive feedback or combine with other measures for sentiment analysis.

Practical Applications

    Sentiment Analysis: Flag positive or negative keywords (e.g., "great," "poor") in customer feedback.

    Categorization: Identify products or services mentioned in text (e.g., "laptop," "support") for categorization.

    Search and Filtering: Enable dynamic filtering in reports based on keyword presence.

    Scoring Systems: Assign scores to text entries based on multiple keywords for ranking or prioritization.

Notes

    Case Insensitivity: The search is not case-sensitive, so "Excellent" and "excellent" are treated the same.

    Exact Matches: KEYWORDMATCH looks for the exact keyword or phrase. For partial matches or more complex text analysis, consider combining with other DAX functions like SEARCH or CONTAINSSTRING.

    Performance: When used on large datasets, ensure the text column is optimized (e.g., avoid excessive unique values) to maintain performance.

    Limitations: It checks for whole words or phrases, not partial matches within words unless specified. For more advanced text parsing, additional DAX or Power Query transformations may be needed.

Example with Multiple Keywords

To score reviews based on multiple keywords (e.g., "excellent" or "great"), you can combine KEYWORDMATCH in a measure:

dax

PositiveFeedbackScore = 

CALCULATE(

    SUMX(

        Reviews,

        KEYWORDMATCH(Reviews[Comment], "excellent", 1, 0) + 

        KEYWORDMATCH(Reviews[Comment], "great", 1, 0)

    )

)

This assigns a score based on the presence of either keyword, summing the matches across rows.

Conclusion

KEYWORDMATCH is a straightforward yet powerful DAX function for text-based analysis in Power BI. It excels in scenarios requiring simple keyword detection, such as sentiment analysis or categorization, and can be extended with other DAX functions for more complex logic. ve that information.