Open In App

Power BI – DAX TEXT Functions

Improve
Improve
Like Article
Like
Save
Share
Report

DAX is a collection of operators, constants, and functions that can be used to compute and return one or more values in an expression or formula. To put it simply, DAX gives you the ability to create new data from data that already exist in your model.

DAX Text Functions 

Tables and columns can be used with DAX Text or String Functions. You may concatenate string values, search for text with string, and return a portion of a string using DAX Text Functions. The Formats for dates, times, and numerals are also adjustable. We have several types of Text Functions in DAX, below is the table showing the DAX text functions in brief:

S.No

DAX – Text Functions

Description

1 BLANK (  ) Returns a blank.
2 LEN ( ) Returns the number of characters in a text string.
3 CONCATENATE ( ) Joins two text strings into one text string.
4 CONCATENATEX ( ) Concatenates the result of an expression evaluated for each row in a table.
5 LEFT ( ) Returns the specified number of characters from the left side of a given text string.
6 RIGHT ( ) Returns the specified number of characters from the right side of a given text string.
7 MID ( ) Returns a string of characters from the middle of a  text string. 
8 UPPER ( ) Converts a text string to all uppercase letters.
9 LOWER ( ) Converts a text string to all lowercase letters.
10 TRIM ( )  Removes all spaces from text.
11 SUBSTITUTE ( )  Replace existing text with new text in a text string.
12 REPLACE ( ) Replaces a part of a text string.
13 EXACT ( )  Compares two text strings and returns TRUE if they are exactly the same, otherwise returns FALSE.
14 FIND ( )  Returns the starting position of one text string within another text string.
15 FORMAT ( ) Converts a value to text according to the specified format.

Dataset

A dataset is a group of data that you connect to or import. Power BI enables you to connect to, import, and combine many datasets in one location. Additionally, dataflows can provide data to datasets. workspaces and datasets are related, and a single dataset might be a component of numerous workspaces. In this dataset below, Employee Dataset , this dataset contains columns such as DeptID, Emp Name, Salary, Bonus, DOJ, Gender, and City. and this dataset contains 35 rows. The source of this dataset is taken from the Excel workbook. Look at the picture below to know about the dataset used.

Employee-dataset

 

Following are the Power BI DAX Text Functions with Examples: 

Blank( )  

Blank function Returns Blank (If the condition is true).

Syntax: BLANK ( )

This Function has no parameters.

Example : If( Emp  [Number of Days] = 0, BLANK ( ), Emp[Salary]/Emp[Number of Days])

Result: BLANK, when Number of Days = 0, from Emp Table. Here Emp Salary is divided by Emp Number of Days so the result will be different instead of 0.

Applying-blank-function

 

Note: 

  1. Nulls are not the same as Blanks.
  2. Both database nulls and empty Excel cells are represented by blanks in DAX.
  3. Empty strings and blanks are not usually the same thing, some procedures may regard them as such.

LEN( )

Len function returns the number of characters in a text string. To Know the length of the given string.

Syntax: LEN ( <COL1>)

Example: Length of Emp Name = LEN (Emp [Emp Name])

Result: This function gives the number of letters from the [Emp Name] column From the Emp table.

Applying-len-function

 

Note: 

  1. In this function Spaces count as characters.
  2. The DAX LEN ( ) implicitly converts non-text data, such as dates or Boolean values, in a column that contains those values to text using the current column format when you use the function with that column.

CONCATENATE( )

Concatenate function is used to join two columns or to create a text String by joining two text strings. The CONCATENATE function combines two text strings into a single text string. The combined items may be text, numbers, Boolean values expressed as text, or any mix of those. If the column has the right values, you can also utilize a column reference.

Syntax: CONCATENATE (<Col 1> , <Col 2>)

Strings can be text or numbers or column references.

Example 1: EmpID With Emp Name=CONCATENATE(Emp[EmpID], Emp[Emp Name])

Result: This function joins two columns  EmpID and Emp Name and gives the result in a single column. (If EmpID is 1 and Emp Name is Abhinav, it gives the result as 1Abhinav).

Applying-concatenate-function

 

Example 2: CONCATENATE( “Tom”, “Cruise”)

Result: Returns as Tom Cruise from two different columns.

Note: 

  1. Only two arguments are allowed for the DAX CONCATENATE Function.
  2. Each text string must be enclosed in double quotation marks if you want to utilize them directly rather than through column references.

CONCATENATEX ( )

In this function, expressions are evaluated for each row in the table, and the results are then concatenated into a single-string result and separated by the designated delimiter. A table or an expression that returns a table is the function’s first argument. The second argument can be either an expression that returns a value or a column containing the values you want to concatenate.

Syntax: CONCATENATEX(Table,Expression, [Delimiter], [OrderBy_Expression1],[Order]…)

The expressions that have to be assessed for every table row, This function returns a single text string.

Example: CityWiseEmps=CONCATENATEX(Emp, Emp[Emp Name], ” , “)

Result: This function gives a delimiter in between column data or two tables.

Applying-concatenatex-function

 

Note: The first parameter for the CONCATENATEX function is either a table or an expression that yields a table. The second parameter might be an expression that returns a value or a column containing the data you want to concatenate.

LEFT ( )

Left function returns the requested number of text characters from the left side of the given string. A single function is sufficient because DAX works with Unicode and stores all characters at the same length

Syntax: LEN (<Text>, <Num_Chars>)

Either a reference to a text-containing column in a table or a text string that contains the characters you want to extract. The number of characters you want from left to extract.

Example 1: Left 1 Letter=LEFT(Emp[Emp Name])

Result: If the Emp Name is ‘John’ then the result will be one letter from the left that means ‘J’

Example 2: Left 2 Letters=LEFT(Emp[Emp Name],2)

Result: If the Emp Name is ”Abhinav” then the result will be two letters from the left that mean ‘Ab’.

Applying-left-function

 

Note: 

  1. With Unicode support, DAX saves all characters with the same length. Therefore, to extract the characters, just one function LEFT is required.
  2. The DAX LEFT function returns the maximum number of characters available and does not cause an error if the num chars argument is a number greater than the number of characters in the text string.

RIGHT( )

Depending on how many characters you enter, this function returns the last characters in the last characters in text string. Regardless of the default language option, RIGHT always counts each character as 1, whether it is a single or double byte.

Syntax: RIGHT(<Text>, [<Num_Times>])

  1. Text: The text string or a reference to a text-containing column that includes the characters you want to remove. It will be implicitly cast to text if the linked column is empty.
  2. Num times: It is Optional, character count for which RIGHT should extract. The default value if omitted is 1. Reference to a column of numbers is another option.

Example: Right4Letters=RIGHT(Emp[EmpName],3)

Result: If the EmpName is “Abhinav” the result shows the last 3 letters of the given string as “nav”.

Applying-right-function

 

Note: 

  1. RIGHT always counts each letter as 1, whether it is a single or double-byte, regardless of the language’s default setting.
  2. Num chars return BLANK if it equals 0. If the num chars are less than zero, #ERROR is returned.

MID( )

MID gives a beginning position, length, and a string of characters from the middle of a text string.

Syntax: MID(<Text>, <Start_Num>, <Num_Chars>)

  1. Text: The text string or text-filled column from which you want to extract the characters.
  2. Start Num: A whole number designating where the first character you want to extract is located. At the commencement of the paragraph, the numerals begin at 1.
  3. Num chars: How many characters should be returned.

Example: MidLetters=MID(Emp[EmpName],3,2)

Result: The result shows 2 string characters from 3rd character. If the EmpName is Abhinav the result will be “hi”.

Applying-mid-function

 

Note: We can extract text from the middle of the input string using the DAX MID function.

UPPER( )

With the help of the upper function, a text string is changed to all uppercase letters.

Syntax: UPPER(<Text>)

The text you want to change to uppercase, or a mention of a text-filled column.

Example: UpperName=UPPER(Emp[EmpName])

Result: The result shows the entire EmpName column will be changed to uppercase letters.

Applying-upper-function

 

Note: No changes will be made to the characters besides alphabet.

LOWER( )

With the help of the lower function, a text string is changed to all lowercase letters.

Syntax: LOWER(<Texxt>)

The text you want to change to uppercase, or a mention of a text-filled column.

Example: LoweName=LOWER(Emp[EmpName])

Result: The result shows the entire EmpName column will be changed to Lowercase letters.

Applying-lower-function

 

Note: No changes will be made to the characters besides the alphabet.

TRIM( )

Trim function removes spaces from left and right and in between.

Syntax: TRIM(<Text>)

Text: The text or text-filled column from which you want to eliminate spaces.

Example : TrimEmpName= TRIM(Emp[EmpName])

Result: You might not be able to see the results of using the TRIM function on a column of text values. To compare the lengths of the strings, you can use the LEN function on the parameter column as well as the resultant column.

Applying-trim-function

 

Note: The TRIM function’s results might not be seen in the computed column when applied to a text column with trailing spaces in DAX. To determine the difference, you can contrast the lengths of the input and output texts.

SUBSTITUTE( )

This function substitutes the existing text with new text.

Syntax: SUBSTITUTE(<Text>, <Old_Text>, <New_Text>)

  1. Text: The text, a reference to a text-containing column, or both, in which you wish to replace any existing text with new text. 
  2. Old text: The current text you want to change.
  3. New text: The text that you want to use in place of old text.

Example: SubstiteName=SUBSTITUTE(Emp[EmpName],”Abhinav”,”Adam”)

Result: The result gives EmpName as Adam instead of Abhinav as per the given function.

Applying-substitute-function

 

Note: 

  1. The SUBSTITUTE function is case-sensitive. SUBSTITUTE won’t replace the text if the case between the old text and the find text is different.
  2. SUBSTITUTE won’t replace the content if within text contains the word “not” and find text is set to “Not”. The DAX SUBSTITUTE function is comparable to the DAX REPLACE function. 

REPLACE( )

Depending on the number of characters you choose, replace a portion of a text string with another text string.

Syntax: REPLACE(<Old_Text>, <Start_Num>, <Num_Chars>, <New_Text>)

  1. Old Text: The text string that contains the replacement characters, or a pointer to a text-filled column.
  2. Start Num: The spot in the old text where you wish to start inserting the new text.
  3. Num Chars: The number of characters you want to change.
  4. New Text: The text to use in place of an old text’s given characters.

Example: ReplaceSalary=REPLACE(Emp[Salary],2,3,999)

Result: Replaces the characters from 2nd character to 3 characters.

Applying-replace-function

 

Note: If you wish to replace any text that appears at a certain position in a text string and is variable in length, you can use the REPLACE function. If you wish to replace a certain piece of text in a text string, you can use the SUBSTITUTE function.

EXACT( )

When two text strings are compared, TRUE is returned if they are exactly the same and FALSE if not. While disregarding formatting variations, EXACT is case-sensitive.

Syntax: EXACT(<Text1>, <Text2>)

Example: CompareEmp=EXACT(“Abhinav”, “Adam”)

Result: If the given String is the same compared to other strings the result will be TRUE or else It will be FALSE.

Applying-exact-function

 

Note: The EXACT function can be used to check the text before it is entered into a document.

FIND( )

Find function Returns the beginning of one text string within another text string.

Syntax: FIND(<Find_Text>, <Within_Text>, [ <Start_Num>], [<NotFoundValue>])

Any text or string that you find using find text, the text that contains the desired text.

StartPosition: It is not required. The starting point for your search should be a character position. Initially, it is 1. It is optional to use NotFoundValue. Usually 0, -1, or BLANK, this value should be returned if the operation fails to identify a matching substring ().

Example: Column=FIND(“Movie”,  “Tom Went To A Movie”)

Result: It shows the starting position of a given string within a string. To get this result go to>>Visualization>>Select, Format >>Choose New column >>write DAX query>>select Table visualization.

Applying-find-function

 

 Note: If the Product name is not stated in the product description, this returns a blank.

FORMAT( )

In this case, a value is transformed into text using the provided format.

Syntax: FORMAT(<Value>, < Format_String>)

  1. Value: The number of expressions that has only one possible outcome.
  2. Format: A string that contains the formatting model.

Example: Date Format=FORMAT(Emp[DOJ], “MM-DD-YYYY”)

Result: It gives the result in the given date format from the Emp Date Of Joining column.

Applying-format-function

 

Note: The FORMAT function delivers an empty string if the value is blank. These are the DAX STRING FUNCTIONS or TEXT FUNCTIONS, DAX some other functions like Logical Functions, Statistical Functions, Date and Time Functions, Filter Functions, Time Intelligence Functions Etc.



Last Updated : 05 Feb, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads