Retrieve value from middle of string in measure

DAX DAX Calculations
1

I have my entire GL in a fact table in my model. I want to pull out the work order number from the posting comment which only appears in single GL. I have a clear delimiter and the W/O is always the same length. I can create a new column in the editor by splitting by delimiter and deleting the unneeded columns, but this seems overkill when it only applies to a few thousand rows of the table.

For example, W/O - 0013453 - BATCH:WL149 should return the value of 0013453.

I can use

W/O = Mid(GL_Detail[Posting Comment],6,7)

to create a new column but I don’t really need this column. I am not sure how to create a measure that will dynamically return the value.

Thanks,

2

Another issue with my calculated column is that in many cases it returns a “DJFOWSK”, which can not be converted to a value and prevents me from linking the work order to my work order lookup table.

3

Hi @jay.majeske,

Welcome to the forum!
You need a Key to identify the record you want to access, once you have that you can either:

Work order number v1 = 
VAR myKey = SELECTEDVALUE( SampleData[KeyColumn] )
RETURN

MID(
    LOOKUPVALUE( SampleData[Posting comment], SampleData[KeyColumn]| myKey ),
    6, 8
)

Or

Work order number v2 = 
MID(
    VALUES( SampleData[Posting comment] ),
    6, 8
)

.
Attached an example. I hope this is helpful.
eDNA - Retrieve value from string.pbix (26.2 KB)
.

Does that mean your data isn’t consistant in structure, try Column from Example inside Power Query.
If you need further assistance please provide a small sample.

1 Like
4

@jay.majeske

In the code below an attempt is made to temporarily convert the mid-value to an integer. If the value is not able to convert then an error occurs. This error is then caught by the ISERROR function and returns the text “Not a number” if an error occurs, otherwise, if it is numeric then the mid values are returned.

GL Code =
SWITCH (
    TRUE (),
    ISERROR (CONVERT (MID (VALUES (Test[Column1]), 6, 8), INTEGER)), 
    "Not a number",
    MID (VALUES (Test[Column1]), 6, 8)
)

eDNA - Retrieve value from string2.pbix (28.6 KB)

1 Like
5

Thanks, that worked great.