Use Dax to Create Column Using Specific Part of Another Column

[bc1985]

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)

[v-kaiyue-msft · Accepted solution]

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.

[v-kaiyue-msft · Accepted solution]

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 · Accepted solution]

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)

[bensiqc]

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.

[sayaliredij]

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]

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?