Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

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 🙂

image.png

 

Regards,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

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 "/":

Calc Table Delimiter.png

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
Community Champion
Community Champion

 

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

 




Lima - Peru
Anonymous
Not applicable

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

@Anonymous 

 

Yes, I confused the Delimiter that you use.

 

I use / instead of \

 

Regards

 

Victor

 




Lima - Peru
Anonymous
Not applicable

@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
Not applicable

That works great, thank you!

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

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

Thanks again!

MCKery

Hey,

 

great to hear that your problem has been solved 🙂

 

Regards,
Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
TomMartens
Super User
Super User

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 🙂

image.png

 

Regards,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.