Forum Discussion

bc1985's avatar
bc1985
New Member
2 years ago
Solved

Use Dax to Create Column Using Specific Part of Another Column

I have a few columns that have values like this:   [Value 1].[Value 2].[Value 3]   I need to create a new column (in DAX, preferably not Power Query due to DirectQuery limitations) that captures ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi bc1985 ,

     

    You can write to the calculated column:

    value3 =

VAR _text = LEFT('Table'[Column1], FIND(".", 'Table'[Column1]))

VAR _len = LEN(_text)

VAR _len2 = LEN('Table'[Column1])

VAR _text2 = MID('Table'[Column1],_len+2,_len2 - _len)

VAR _text3 = LEFT(_text2, FIND(".", _text2))

VAR _len3 = LEN(_text3)

VAR _text4 = MID(_text2,_len3+2,_len2-_len3)

RETURN

LEFT(_text4,LEN(_text4)-1)

     

    The final result is shown below:

     

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

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

  • bensiqc's avatar
    bensiqc
    2 years ago

    An update: it seems the reason "FIND" works for my formula but not yours is because you need to use the four argument version for DirectQuery as there is no equivalent two argument version in SQL. I made the adjustment and yours works for me!

    For posterity, here is the full function that worked:
    value3=

    VAR _len3 = LEN(_text3)

    VAR _text4 = MID(_text2,_len3+2,_len2-_len3)

    RETURN


    VAR _text = LEFT(Table1[Column], FIND(".", Table1[Column],1,0))

    VAR _len = LEN(_text)

    VAR _len2 = LEN(Table1[Column])

    VAR _text2 = MID(Table1[Column],_len+2,_len2 - _len)

    VAR _text3 = LEFT(_text2, FIND(".", _text2,1,0))

    VAR _len3 = LEN(_text3)

    VAR _text4 = MID(_text2,_len3+2,_len2-_len3)

    RETURN

    LEFT(_text4,LEN(_text4)-1)

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi bc1985 ,

 

You can write to the calculated column:

value3 =

VAR _text = LEFT('Table'[Column1], FIND(".", 'Table'[Column1]))

VAR _len = LEN(_text)

VAR _len2 = LEN('Table'[Column1])

VAR _text2 = MID('Table'[Column1],_len+2,_len2 - _len)

VAR _text3 = LEFT(_text2, FIND(".", _text2))

VAR _len3 = LEN(_text3)

VAR _text4 = MID(_text2,_len3+2,_len2-_len3)

RETURN

LEFT(_text4,LEN(_text4)-1)

 

The final result is shown below:

 

If your Current Period does not refer to this, please clarify in a follow-up reply.

 

Best Regards,

Clara Gong

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

bensiqc's avatar
bensiqc
Helper I
2 years ago

Inexplicably, I get an error saying "Function 'FIND' is not allowed as part of calculated column DAX expressions on DirectQuery models". Very strange since my DAX before did not throw that error. DirectQuery restrictions seem arbitrary and inconsistent.