TEXT FUNCTIONS
COMBINEVALUES:
Joins two or more text strings into one text string. The primary purpose of this
function is to support multi-column relationships in DirectQuery models. See
Remarks for details.
Syntax:
COMBINEVALUES(<delimiter>, <expression>, <expression>[, <expression>]…)
Example:combine column = Customer[Customer]&"_"&Customer[State-
Province]&"_"&Customer[Country-Region]
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
-
CONCATENATE
Joins two text strings into one text string.
Syntax:
CONCATENATE(<text1>, <text2>)
Example :CONCAT = CONCATENATE(Customer[Customer],Customer[State-Province])
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
-
FIND
Returns the starting position of one text string within another text string. FIND
is case-sensitive.
Syntax:
FIND(<find_text>, <within_text>[, [<start_num>][, <NotFoundValue>]])
Example :
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
SEARCH
Returns the number of the character at which a specific character or text string is
first found, reading left to right. Search is case-insensitive and accent
sensitive.
Syntax :
SEARCH(<find_text>, <within_text>[, [<start_num>][, <NotFoundValue>]])
Example :Search = SEARCH("a",Customer[Customer],1,0)
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
LEFT
Returns the specified number of characters from the start of a text string.
Syntax :
LEFT(<text>, <num_chars>)
Example :Cpmpanycode = LEFT(Customer[Customer ID],2)
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
RIGHT
RIGHT returns the last character or characters in a text string, based on the
number of characters you specify.
Syntax :
RIGHT(<text>, <num_chars>)
Example :id_number = RIGHT(Customer[Customer ID],5)
-----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
LEN
Returns the number of characters in a text string.
Syntax :
LEN(<text>)
Example : LENGTH OF CHARS = LEN(Customer[Customer])
-----------------------------------------------------------------------------------
--------------------------------------------------------------------------------
MID
Returns a string of characters from the middle of a text string, given a starting
position and length.
Syntax :
MID(<text>, <start_num>, <num_chars>)
Example :MID = MID(Customer[Customer ID],3,3)
-----------------------------------------------------------------------------------
--------------------------------------------------------------------------------
UPPER
Converts a text string to all uppercase letters.
Syntax :
UPPER (<text>)
Example :UPPER = UPPER(Customer[Customer])
-----------------------------------------------------------------------------------
--------------------------------------------------------------------------------
LOWER
Converts all letters in a text string to lowercase.
Syntax:
LOWER(<text>)
Example :LOWER = LOWER(Customer[Customer])
-----------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SUBSTITUTE
Replaces existing text with new text in a text string.
Syntax:
SUBSTITUTE(<text>, <old_text>, <new_text>, <instance_num>)
Example :replace = SUBSTITUTE(Customer[Country-Region],"United States","USA")
-----------------------------------------------------------------------------------
--------------------------------------------------------------------------------
TRIM
Removes all spaces from text except for single spaces between words.
Syntax :
TRIM(<text>)
Example :TRIM = TRIM(Customer[Customer])
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
-
FORMAT
Converts a value to text according to the specified format.
Syntax :
FORMAT(<value>, <format_string>[, <locale_name>])
Example := format(date(date),"dd")
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
-
