In this tutorial, we will focus on the Left and Right functions in DAX, which are used to extract a specified number of characters from the left or right side of a text string.
The Left Function
The Left function in DAX returns a specified number of characters from the left side of a text string. The syntax for the Left function is as follows:
“`
LEFT(text, num_chars)
“`
– `text` is the text string from which you want to extract characters.
– `num_chars` is the number of characters you want to extract from the left side of the text string.
For example, if you have a column named “Product Name” and you want to extract the first three characters from each product name, you can use the Left function as follows:
“`
LEFT([Product Name], 3)
“`
This will return a new column with the first three characters from each product name.
The Right Function
The Right function in DAX returns a specified number of characters from the right side of a text string. The syntax for the Right function is similar to the Left function:
“`
RIGHT(text, num_chars)
“`
– `text` is the text string from which you want to extract characters.
– `num_chars` is the number of characters you want to extract from the right side of the text string.
For example, if you have a column named “Order Number” and you want to extract the last four characters from each order number, you can use the Right function as follows:
“`
RIGHT([Order Number], 4)
“`
This will return a new column with the last four characters from each order number.
Using Left and Right Functions in Power BI
The Left and Right functions in DAX can be useful in various scenarios. Here are a few examples:
1. Extracting the first name and last name from a full name column:
– To extract the first name, you can use the Left function with a delimiter like a space or comma.
– To extract the last name, you can use the Right function with a delimiter like a space or comma.
2. Extracting the year, month, or day from a date column:
– To extract the year, you can use the Left function with the appropriate number of characters.
– To extract the month or day, you can use the Right function with the appropriate number of characters.
3. Extracting a specific part of a text string based on a pattern:
– You can use the Left and Right functions in combination with other DAX functions like FIND or SUBSTITUTE to extract a specific part of a text string based on a pattern.
Conclusion
The Left and Right functions in Power BI DAX provide a simple and efficient way to extract a specified number of characters from the left or right side of a text string. These functions can be used in various scenarios to manipulate and analyze text data. By understanding how to use the Left and Right functions, you can enhance your data analysis capabilities in Power BI.