Extracting English Names from Other Language Text Using DAX in Power BI

 Extracting English Names from Other Language Text Using DAX in Power BI

Dax Formula:

EnglishName =
VAR TextToSearch = 'YourTableName'[YourColumnName]
VAR EnglishChars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"
VAR NameStart = MINX(
    FILTER(
        ADDCOLUMNS(
            GENERATESERIES(1, LEN(TextToSearch), 1),
            "Character", MID(TextToSearch, [Value], 1)
        ),
        CONTAINSSTRING(EnglishChars, [Character])
    ),
    [Value]
)
VAR NameEnd = MAXX(
    FILTER(
        ADDCOLUMNS(
            GENERATESERIES(1, LEN(TextToSearch), 1),
            "Character", MID(TextToSearch, [Value], 1)
        ),
        CONTAINSSTRING(EnglishChars, [Character])
    ),
    [Value]
)
RETURN MID(TextToSearch, NameStart, NameEnd - NameStart + 1)

Explanation:

VAR TextToSearch = 'YourTableName'[YourColumnName]:

This line creates a variable named TextToSearch and assigns the value of the column 'YourTableName'[YourColumnName] to it. It stores the Arabic text you want to process.

VAR EnglishChars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ":

This line creates a variable named EnglishChars and assigns a string containing all English alphabet characters (both lowercase and uppercase) to it.

VAR NameStart = MINX(...):

This line creates a variable named NameStart. It uses the MINX function to find the minimum position of an English character in the text.
The FILTER function is used to filter characters from the generated series that belong to the English alphabet.
ADDCOLUMNS is used to add a column named "Character" to the generated series, which contains each character from the text.
CONTAINSSTRING filters only English characters, and MINX finds the minimum position.

VAR NameEnd = MAXX(...):

Similar to NameStart, this line creates a variable named NameEnd to find the maximum position of an English character in the text.

RETURN MID(...):

This line uses the MID function to extract the substring from the original text (TextToSearch).
It starts from the position NameStart and ends at the position NameEnd, effectively extracting the English name from the Other Language text.

Remember to replace 'YourTableName' with the name of your table and 'YourColumnName' with the name of the column containing the Arabic text. This DAX function is designed to find and extract English names from the Arabic text.

Feel free to ask any questions about Power BI. I am here to help you.

Happy Learning! 😊





Comments

Post a Comment

Popular posts from this blog

Create Calendar Table in Power BI using DAX Function

 Calendar Table using Dax Syntax: CalendarTable = ADDCOLUMNS ( CALENDAR (DATE(2024, 1, 1), DATE(2024, 12, 31)), "Year", YEAR([Date]), "MonthNumber", MONTH([Date]), "MonthName", FORMAT([Date], "MMMM"), "Quarter", QUARTER([Date]), "DayOfWeek", WEEKDAY([Date]), "DayOfMonth", DAY([Date]), "WeekdayName", FORMAT([Date], "dddd"), "IsWeekend", IF( OR(WEEKDAY([Date]) = 1, WEEKDAY([Date]) = 7), TRUE(), FALSE()), "IsHoliday", IF( OR( MONTH([Date]) = 1 && DAY([Date]) = 1, MONTH([Date]) = 7 && DAY([Date]) = 4), TRUE(), FALSE()) ) Explanation: CALENDAR (DATE(2024, 1, 1), DATE(2024, 12, 31)) Creates a table with a single column containing dates from January 1, 2024, to December 31, 2024. ADDCOLUMNS ( Adds new columns to the table created by the CALENDAR function. "Year", YEAR([Date]), Adds a column named "Year" that...
Read more

Calculate Sales for Last Year Month to date

Sameperiodlastyear till to date based on selection in year and month selection Problem:  If the current year and month are selected, sales data from the same period of the previous year up to the current date is needed. For instance, if it's March 2024, you'd require sales data from March 2023 up to today's date. If the selection is for a different year and month, such as January 2023, you need the entire sales data for January 2022. Solution:  For this scenario you need to create Three Dax Measures Measure 1 = (This measure is to calculate current month sales till the date) CALCULATE( SUM(CST[Total Financed Amount]), SAMEPERIODLASTYEAR( DATESYTD('Calendar Table'[Date]) ), FILTER( ALL('Calendar Table'), 'Calendar Table'[MonthNum] = MONTH(NOW()) && DAY('Calendar Table'[Date]) <= DAY(NOW()) ) ) Explanation: CALCULATE : This is a DAX function used to modify or filter the context for a calculation. It can apply additional filters to a ...
Read more

CONTAINSTRING DAX

Exploring the CONTAINSSTRING Function in DAX Understanding the CONTAINSSTRING Function At its core, the CONTAINSSTRING function enables users to determine whether a specific substring exists within a given text string. This function returns a Boolean value, TRUE if the substring is found, and FALSE if it is not. Here's the basic syntax of the CONTAINSSTRING function: CONTAINSSTRING(<text>, <substring>) <text>: The text string within which you want to search. <substring>: The substring you're searching for within the text. Practical Example: Using CONTAINSSTRING in DAX Let's delve into a practical example to grasp how the CONTAINSSTRING function works within the context of DAX. Suppose we have a dataset containing product information, including a column named ProductName. We're interested in identifying which product names contain the word "apple". We can achieve this by creating a calculated column using the CONTAINSSTRING functi...
Read more