DAX Function to Sort Hashtags in Ascending Order

Objective

Create a DAX function that takes a column of hashtags and returns the sorted version of these hashtags in ascending alphabetical order.

Function Definition

This function will be defined to manipulate a text column, specifically to sort the hashtags present in that column. We will utilize DAX functions such as CONCATENATEX, SPLIT, and ORDER BY for the implementation.

Code Implementation

SortedHashtags = 
VAR HashtagTable = 
    ADDCOLUMNS(
        GENERATESERIES(1, PATHLENGTH('[hash]')),
        "Hashtag", 
        TRIM(MID(
            '[hash]', 
            PATHITEM('[hash]', [Value]), 
            FIND(" ", '[hash]& ",", PATHITEM('[hash]', [Value])-1) - PATHITEM('[hash]', [Value]) + 1))
    )
VAR SortedHashtagList =
    SELECTCOLUMNS(
        FILTER(
            HashtagTable,
            [Hashtag] <> "")
        , "Hashtag", [Hashtag] 
    )
RETURN
    CONCATENATEX(
        SortedHashtagList,
        [Hashtag],
        " ",
        [Hashtag],
        ASC
    )

Documentation

Function: SortedHashtags

• Purpose: Sorts the hashtags in the column [hash] in ascending alphabetical order.
• Parameters:
  • [hash]: A text string containing multiple hashtags separated by spaces.
• Returns: A single string of hashtags sorted in ascending order, separated by spaces.
• Exceptions:
  • If the input column is empty or does not contain valid hashtags, the function will return an empty string.

Explanation of the Code

1. Variable Declaration (HashtagTable):

   • The ADDCOLUMNS function generates a table that splits the original hash string into separate hashtags.
   • GENERATESERIES is used to create a series that allows us to iterate through the individual hashtags using PATHLENGTH.

2. Filtering and Cleaning:

   • The TRIM function is used to ensure that any leading or trailing spaces from each hashtag are removed.

3. Sorting:

   • SELECTCOLUMNS is used to filter out any empty hashtags.

4. Concatenation:

   • CONCATENATEX is used to concatenate the sorted hashtags back into a single string. The sorting is applied using the last two parameters which specify that the sorting should be in ascending order by the value of the Hashtag.

Usage Example

Assuming we have a table Posts with a column named hash that contains:

To Utilize the Function:

You would call the function in a new calculated column or measure as follows:

SortedHashtags = SortedHashtags

Resulting Output After Sorting:

Conclusion

The above DAX function effectively sorts hashtags in ascending order, enabling better data management and display in reports. For further knowledge enhancement in DAX and its functionalities, consider exploring learning resources available on the Enterprise DNA Platform.