Splitting columns by Delimiter with DAX (No Query Editor)

[Anonymous]

Hi,

I have values in a column like, " Goext.wk.rainier.global". I would like to split the column by the right most delimiter so that the value in the column is just Goext.

I know I can (and have done it before in Query Editor), but is there a way to do it with DAX? Is there a formula for splitting by delimiter? I am asking because I am facing issues with the query editor and this can longer be done by the split column option.

[Syndicate_Admin]

Good day, I have problems with the editor for issues of permissions to the sql base, and I need that by means of DAX, to be able to separate the info, and get the numbers of 9 digits that I emphasize, A help. Thank you

[Anonymous]

@Anonymous 

For such modelling, It is always best to use split function in Query Editor, I am not sure why you are not able to do so.

The solutions are provided above with dax, but you should know this is not split column, it just find the text from the column values. In DAX it is kind of  limited to if you only want the most LEFF/RIGHT value of delimiter. 

RIGHT('Table'[Column],SEARCH(".",[Column],1,0))

Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[edhans]

You said right-most delimiter, but then put Geotext as the answer. that is the left-most.

For the right-most, to return Global, use this:

Column = 
TRIM (
    MID (
        SUBSTITUTE ( [Column1], ".", REPT ( " ", LEN ( [Column1] ) ) ),
        ( LEN ( [Column1] ) - LEN ( SUBSTITUTE ( [Column1], ".", "" ) ) )
            * LEN ( [Column1] ) + 1,
        LEN ( [Column1] )
    )
)

For the left most, use this:

Column 2 = 
    LEFT(
        [Column1],
        FIND(".",[Column1]) - 1
    )

I am curious about your issues with the Power Query editor. It is designed for data modeling. DAX is designed for analysis, not modeling. See issues below with extensive data modeling in DAX vs your source or Power Query. The tables in DAX are not like Excel spreadsheets. Can you share more about your issues with the PQ editor?

In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
Creating a Dynamic Date Table in Power Query

[amitchandak]

@Anonymous , try like this in dax

new column = left([column], search(".",[column],1,0)-1)