DAX has no built-in SPLIT function. To separate a delimited value, use Power Query’s Split Column feature when you need flexible splitting or separate rows; in a DAX calculated column, functions such as PATHITEM or SEARCH can extract a particular piece from text.
Why DAX has no SPLIT function
DAX is designed primarily to calculate values in a data model, not to perform general-purpose text transformations. Its expressions return a scalar value, such as a number, date, or text string, in a calculated column or measure. Splitting one text value into a variable number of text values is a different kind of operation: it can produce several fields or several rows, depending on what the data needs.
That distinction explains why searching for a DAX equivalent to Excel’s or another language’s SPLIT function can be frustrating. There is no DAX function named SPLIT that takes a string and delimiter and returns an array of pieces. Instead, choose an approach based on the result you want:
- Use Power Query to split a source column into multiple columns or rows during data preparation.
- Use DAX text functions to extract a known-position item into a calculated column.
- Use a separate, normalized table when each item in a delimited list needs to participate in relationships or filtering.
For most imported data, Power Query is the natural first choice. DAX is useful when a model calculation needs one specific token and changing the query is impractical.
Choose the method by the shape of the result
Before writing an expression, decide whether the output should be one value, several columns, or multiple rows. A value such as North|Retail|Active might mean three attributes belonging to one record, or it might represent three separate categories attached to that record. Those interpretations lead to different model designs.
- One specific piece: Extract the first or second token into a calculated column if the number and meaning of positions are stable.
- Several fixed pieces: Split into columns in Power Query, especially when the fields have distinct meanings such as city, state, and postal code.
- A list of repeating items: Split into rows in Power Query and keep a key linking each item back to its original record.
- Just display the original text: Keep it as text. Splitting adds complexity if no separate analysis or filtering is needed.
Do not confuse splitting with finding text. DAX can search for a substring or replace a delimiter, but those operations alone do not create a variable-length collection that a measure can iterate over as if it were an array.
Split a column in Power Query
Power Query is usually the clearest solution when the data is imported or can be transformed before it enters the model. In Power BI Desktop, open Transform data to launch Power Query Editor, select the column, then choose Transform > Split Column > By Delimiter. Select the delimiter and configure the split options. In Advanced options, choose whether to split into columns or rows where that option is available.
For example, suppose a table has a ProductID column and a Tags column containing red,green,blue. Splitting on a comma into rows gives one row per product-tag pairing. Keep ProductID on each resulting row so the tag can still be associated with its product. If you instead split into columns, the result has fields such as Tags.1, Tags.2, and Tags.3; that can be suitable for a fixed set of positions, but less convenient for lists of varying length.
In Excel for Microsoft 365, Power Query is available from Data > Get Data or through the workbook’s existing query connections; the exact entry point depends on how the data was loaded. In Power BI, use Home > Transform data. The editor’s split options are more appropriate than a DAX expression when you need to create multiple output rows. Power Query transformations are applied when the query refreshes, rather than recalculated as a report user changes a slicer.
Extract a token with PATHITEM
PATHITEM can retrieve an item at a known position from a pipe-delimited path. It is not a general split function, but it can be a compact workaround if your source delimiter can safely be changed to a vertical bar. In this method, SUBSTITUTE replaces the source delimiter with the pipe separator that PATHITEM expects.
Assume the table is named Products, its text column is Tags, and each value looks like Red,Green,Blue with commas separating items and no pipe characters already present. Create a calculated column with this DAX expression:
FirstTag = PATHITEM(SUBSTITUTE(Products[Tags], ",", "|"), 1)
For a row where Products[Tags] is Red,Green,Blue, the calculated column returns Red. To return the second item, use the same assumed table and data layout, changing the position to 2:
SecondTag = PATHITEM(SUBSTITUTE(Products[Tags], ",", "|"), 2)
That row returns Green. The position is one-based: 1 means the first item. This approach is convenient for a small number of known positions, but it has important limitations. A value that lacks a requested position will not give you a useful token, and an original pipe character can be mistaken for a separator. Delimiters surrounded by inconsistent spaces may also leave spaces in the extracted result. Clean the input or use a more explicit extraction method if those cases occur.
Extract text with SEARCH and LEFT
When you only need the text before the first delimiter, ordinary text functions can be easier to understand. Assume a table named Items has a column Raw, and the delimiter is a comma. Add this calculated column:
FirstPart = VAR SourceText = Items[Raw] VAR DelimiterPosition = SEARCH(",", SourceText, 1, 0) RETURN IF(DelimiterPosition = 0, SourceText, LEFT(SourceText, DelimiterPosition - 1))
If Items[Raw] is pear,plum, the result is pear. If there is no comma, the expression returns the full original text. The fourth argument to SEARCH supplies a value of 0 when the delimiter is absent, so the expression does not depend on a search error for that case. This example does not trim spaces: for input pear, plum, the first result is pear, while extraction of the second part would need additional logic to remove the leading space.
For a particular later piece, you can combine searches and MID, but the expression becomes less maintainable as the number of delimiters grows. If the source can contain missing pieces, repeated delimiters, or quoted text containing commas, a simple search-based expression may not follow the intended data rules. Define how those cases should behave before choosing a formula.
Why splitting into rows is different
A delimited list stored in one cell is often a sign that the model contains a repeating attribute. For instance, if one customer can have many interests, a single value such as Gardening|Cycling|Cooking is difficult to filter and count reliably as separate interests. A more useful structure is a customer table plus a related customer-interest table with one row per customer-interest pair.
Power Query can create those rows during refresh. DAX calculated columns, by contrast, calculate one value for each existing row; they do not dynamically expand that row into a variable number of rows. DAX does support table expressions in other modeling contexts, but that does not make a scalar text expression into a general-purpose split operation. When the goal is a proper one-to-many relationship or item-level analysis, prepare a separate table instead of trying to make a measure behave like a parser.
Common problems and data-quality checks
Many failed split attempts are caused by irregular source text rather than a missing function. Check the input before building a workaround, and decide what the output should be for each exceptional case.
- Inconsistent delimiters: Values may use commas, semicolons, or a mixture. Standardize them in Power Query or handle each documented format deliberately.
- Extra spaces: A value such as
red, greenincludes a space after the comma. Trim extracted values or clean the source before loading. - Empty items: Consecutive delimiters, such as
red,,blue, imply a blank item. Decide whether to keep or remove it. - Delimiter inside a value: A comma may be part of a quoted name rather than a separator. A simple DAX search cannot reliably parse every quoting convention; use a transformation that understands the source format.
- Unexpected pipe characters: The
PATHITEMworkaround uses pipes as separators, so replacing commas is unsafe if pipes can already occur in the data. - Blank source values: Test blanks and nulls explicitly in the chosen transformation so they do not create misleading output.
A small sample with ordinary, blank, malformed, and delimiter-containing values is a useful validation set. Compare the transformed output with the original records before relying on it in relationships or reports.
DAX versus Power Query and Excel functions
Power Query and DAX serve different stages of a model. Power Query shapes data at refresh time and can change the number of rows or columns. DAX calculates model values after loading and is closely connected to filter context, relationships, and report calculations. Use the query layer for data preparation where possible; use DAX when the result genuinely belongs in model calculation.
Excel for Microsoft 365 also has a worksheet function named TEXTSPLIT, but that is an Excel function, not DAX. Its availability does not mean the same function can be used in a Power BI measure or calculated column. Likewise, a formula copied from Excel may not be valid DAX even if both languages use familiar function names. Keep the formula in the environment where it will run, and check the syntax for that language.
There is no additional license or separate DAX add-in needed to use the DAX functions discussed here in a compatible data model. The practical cost is maintenance: a short calculated column can be easy to support, while a long expression that repeatedly parses inconsistent strings can be difficult to debug. If a source system can provide the values as separate fields or a related table, that is often a better long-term fix.
Frequently Asked Questions
Does DAX have a SPLIT function?
No. DAX has text functions that can search, replace, and extract text, but no general-purpose SPLIT function that returns an array of tokens. Use Power Query for a full split, or extract a known item with DAX functions such as PATHITEM.
How do I split a comma-separated column in Power BI?
Choose Home > Transform data, select the column in Power Query Editor, and use Transform > Split Column > By Delimiter. Select comma and choose columns or rows according to the structure you need.
Can a DAX measure split text into multiple rows?
No. A measure returns a scalar result in the current evaluation context; it does not expand model rows. Split into rows in Power Query and model the resulting table if each token needs separate filtering or analysis.
Can PATHITEM replace a delimiter other than a pipe?
It can be used after SUBSTITUTE changes a safe source delimiter to a pipe. Check first that the original text does not contain pipes and that blanks, spaces, and missing positions are handled as intended.
Is TEXTSPLIT available in DAX?
No. TEXTSPLIT is an Excel worksheet function, not a DAX function. For a DAX model, use Power Query to split the data or write a DAX expression to extract a specific piece.
Related
- What Is a String in JavaScript? Types, Uses, and Examples
- JavaScript split() Syntax, Examples, and Pitfalls
- F-String in Java: Formatting and Alternatives
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Python len() of String: How It Works & ExamplesThe len() function in Python returns the number of characters in a string. For example, len("hello") returns 5.…
- Excel Compare Two Sheets for Matching Names: FormulasTo compare names in two worksheets, enter a formula beside the names on the first sheet and check…
- JavaScript String startsWith() Function: How It WorksYes. JavaScript provides the string method startsWith() to check whether a string begins with a specified sequence of…
- W3Schools String Functions in JavaScript ExplainedOn W3Schools, “string functions” usually means JavaScript string methods: operations such as searching, replacing, slicing, and changing the…
- Excel SUMIF Starts With Text: Formula & ExamplesYes, you can use SUMIF to sum values where a corresponding text cell starts with specific characters, by…
- How to Apply a Filter in Excel to Find Data FastTo apply a filter in Excel, click a cell in your data, then choose Data > Filter. Use…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.