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 Value 3. Is there a way to do this? Value 1 is easy enough as I can use a mid formula to capture everything between [ and ], but Value 3 is a bit tricker. Note: Values 1, 2, and 3 can be any number of characters so I can't make any assumptions about the length of those strings of text.

 

I used something like this to capture value 1, FYI:

Client Code 1 Title = mid(CustomerBillingSummaryParams[ClientCd01DimensionTxt],2,find("]",CustomerBillingSummaryParams[ClientCd01DimensionTxt],1,0)-2)
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)

5 Replies

  • sayaliredij's avatar
    sayaliredij
    Solution Sage
    2 years ago

    Hi bc1985 

     

    I am assuming delimiter will be always going to be '.'

    PLease check following formule for calculated column 

     

    value3 = RIGHT('Table'[Column1], FIND(".", 'Table'[Column1] & ".")-1)
     

    Thanks and Regards,

    Sayali

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful!! 

    • 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. Would there be a way to do this without find/search? Or is there any explanation for why 'find' works in my formula above but not here?

  • 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.

    • bensiqc's avatar
      bensiqc
      Helper I
      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)