cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MCKERY
Resolver II
Resolver II

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 II
Super User II

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

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

Vvelarde
Community Champion
Community Champion

@MCKERY 

 

Yes, I confused the Delimiter that you use.

 

I use / instead of \

 

Regards

 

Victor

 




Lima - Peru

@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).

That works great, thank you!

Hey @MCKERY ,

 

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

@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 II
Super User II

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.