Take text after a delimiter, DirectQuery

[Anonymous]

Hello,

I have a DirectQuery that pulls a column that has values like below. Normally if this was a data import, I would simply extract the text after the delimiter using PowerQuery M. This is not permitted with DirectQuery, however. How do I create a measure to reference the value after the delimiter "\" to be used in a table visual relative to another column in the same table??

COMPANY\PAUL

COMPANY\JOHN

COMPANY\MICHAEL

COMPANY\REBECCA

COMPANY\MAIGHAN

into 

PAUL

JOHN

MICHAEL

REBECCA

MAIGHAN

[Anonymous]

If you want to use the Names as filters (such as on rows or columns) it needs to be a table as you cannot use measure as filters. I believe you can create calculated columns in DirectQuery, or at least I was able to using DQ to the Adventure Works database. With that being said, take a look at this code for a new calculated column. In this example I was extracting the numbers after "SO", you would obviously use "/":

Table 2 = 
Var __Delimiter = "O"
Return 

SELECTCOLUMNS(
    ADDCOLUMNS(
        DISTINCT( FactInternetSales[SalesOrderNumber] ), 
        "Deliminated", 
            MID(
                [SalesOrderNumber], 
                SEARCH(__Delimiter,[SalesOrderNumber])+1, 
                LEN([SalesOrderNumber]) - SEARCH(__Delimiter,[SalesOrderNumber])
            ) 
    ), 
    "New List", 
    [Deliminated]
)

[Vvelarde]

Hi  you can use this measure

Name =
VAR _User =
    SELECTEDVALUE ( Table1[User] )
VAR _FindDelimiter =
    FIND ( "/"; _User; 1 ) + 1
RETURN
    IF (
        HASONEVALUE ( Table1[User] );
        MID ( _User; _FindDelimiter; LEN ( _User ) - _FindDelimiter + 1 )
    )

Regards

Victor

[Anonymous]

@Vvelarde  "...The search Text provided to function 'FIND' could not be found in the given text...". Thank you for your efforts!

[Vvelarde]

@Anonymous 

Yes, I confused the Delimiter that you use.

I use / instead of \

Regards

Victor

[Anonymous]

@Vvelarde I did try both "\" and "/" when I noticed that, but it still returned the same error. Thanks again, I did find a measure that has the desired outcome (and am learning how it works, for next time).

[Anonymous]

That works great, thank you!

[TomMartens]

Hey @Anonymous ,

I'm a little confused, as your latest post sounds like none ot the provided solutions will work for, but nevertheless you accepted my post as an answer, can you place post if there is still an issue?
If this is the case, please provide the DAX that your are using.

Regards,
Tom

[Anonymous]

@TomMartens  Apologies! I responded to the incorrect post. Your solution worked. I have corrected my replies.

Thanks again!

MCKery

[TomMartens]

Hey,

great to hear that your problem has been solved 🙂

Regards,
Tom

[TomMartens · Accepted solution]

Hey,

maybe you can add a calculated column to your table like so:

Column = 
var theText = 'Table1'[aname]
var thePosition = FIND("\",theText,1,0)
return
MID(theText,thePosition + 1,LEN(theText)-thePosition)

then you can use the new column also as a slicer.
The DAX for a measure will look like this. it's a little bit more verbose to make sure that a single value of the column with the name is present:

Measure = 
IF(HASONEVALUE(Table1[aname])
    ,var theText = FIRSTNONBLANK('Table1'[aname],0)
    var thePosition = FIND("\",theText,1,0)
    return
    MID(theText,thePosition + 1,LEN(theText)-thePosition)
)

This is my testdata 🙂

Regards,

Tom