Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Take text after a delimiter, DirectQuery

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

Need Help
  • TomMartens's avatar
    TomMartens
    7 years ago

    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

     

10 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    7 years ago

    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

     

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    7 years ago

     

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      That works great, thank you!

      • TomMartens's avatar
        TomMartens
        Super User
        7 years ago

        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's avatar
      Anonymous
      Not applicable
      7 years ago

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

      • Vvelarde's avatar
        Vvelarde
        Community Champion
        7 years ago

        Anonymous 

         

        Yes, I confused the Delimiter that you use.

         

        I use / instead of \

         

        Regards

         

        Victor

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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]
)