13. DAX: Data Analysis Expressions
13.14 Text Functions
-- CONCATENATE joins exactly TWO values; use & for more
Store Label = CONCATENATE(DarkStore[Store Name], " – " & DarkStore[City])
Customer Label = Customer[Customer Name] & " (" & Customer[City] & ")"
Platform Code = LEFT(Orders[Order ID], 3) -- "BLK"
Store Number = RIGHT(DarkStore[Store ID], 2) -- "01"
City Code = MID(DarkStore[Store ID], 5, 3) -- "PUN"
Name Length = LEN(Customer[Customer Name])
Clean Name = UPPER(TRIM(Customer[Customer Name]))
Short Month = FORMAT('Date'[Date], "MMM-YY") -- "Mar-25"
Sales Text = "Total sales: ₹ " & FORMAT([Total Sales], "#,##0")
Under 10 Text = FORMAT([% Orders Delivered Under 10 Mins], "0.0%")
Category List = CONCATENATEX(VALUES(Product[Category]), Product[Category], ", ")
Other useful text functions: LOWER, SUBSTITUTE, REPLACE, SEARCH (not case-sensitive), FIND (case-sensitive), CONTAINSSTRING, VALUE (text to number).
FORMAT returns text
FORMAT converts a number into text. A measure that returns FORMAT output cannot be used as a number in charts and sorts alphabetically. Prefer the Format options in Measure tools (or dynamic format strings) for display formatting.
Ravindra Bagale's Tip
Friends, many students build row-level text columns in DAX (splitting names, cleaning codes) that belong in Power Query. Do row-level text work in Power Query, where it runs once at refresh and compresses better. Use DAX text functions mainly for labels and titles in measures. Clear?
Ravindra Bagale's Tip – मराठी
मित्रांनो, बरेच students Power Query मध्ये असायला हवे असे row-level text columns (नावं split करणं, codes साफ करणं) DAX मध्ये बनवतात. Row-level text चं काम Power Query मध्ये करा, जिथे ते refresh च्या वेळी एकदाच चालतं आणि जास्त चांगलं compress होतं. DAX text functions मुख्यतः measures मधल्या labels आणि titles साठी वापरा. समजलं का?
Ravindra Bagale's Tip – हिंदी
दोस्तों, बहुत से students ऐसे row-level text columns (नाम split करना, codes साफ़ करना) DAX में बनाते हैं जो Power Query में होने चाहिए. Row-level text का काम Power Query में करो, जहाँ वह refresh पर एक बार चलता है और बेहतर compress होता है. DAX text functions मुख्य रूप से measures में labels और titles के लिए इस्तेमाल करो. समझ आया?
Loading DAX practice tasks...
- Short company code (Easy): Calculated column on Accounts: Code = first 3 letters of Company.
- Country in capitals (Easy): Calculated column on Accounts: Country Caps = Country in upper case.
- Length of the name (Easy): Calculated column on Accounts: Name Length = number of characters in Company.
- Company with country (Easy): Calculated column on Accounts: Label = Company, a space, then Country in round brackets.
- Number part of the ID (Medium): Calculated column on Subscriptions: Sub No = last 2 characters of SubID.
- First word of the company (Medium): Calculated column on Accounts: First Word = letters before the first space in Company.
- Shorter plan name (Medium): Calculated column on Subscriptions: Plan Short = Plan with "Enterprise" replaced by "Ent".
- Monthly value as text (Hard): Calculated column on Subscriptions: MRR Text = Seats × PricePerSeat shown with a thousands comma and no decimals.
- Companies with "an" in the name (Hard): Measure: Has AN = number of Accounts rows whose Company contains "an" (any case).
- List of countries (Hard): Measure: Country List = all different Country values joined with ", " in A to Z order.
Revision card
Comparison card