cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jvanmeter Frequent Visitor
Frequent Visitor

Extracting First DN out of Distinguished Name

How Do I Extract the Text Found at the First DN= of the Distinguished Name?

 

Here is a Distinguished Name:

 

CN=Test,OU=Iowa,OU=Primary,DC=NA,DC=US,DC=COM

CN=Test,OU=Primary,DC=NA,DC=US,DC=COM

 

I want the output of the above to be NA

 

Essentially it would find the first occurnance from the left of DC= and return the text after that untill the next ,

 

This would give me the domain of each user.

 

Thank You Ahead of Time. 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Extracting First DN out of Distinguished Name

OK, that means that some of your values don't have a DC= in them so you will want to use the "Not Found Value" parameter of FIND. https://msdn.microsoft.com/en-us/query-bi/dax/find-function-dax

 

So, something like:

 

Column = 
VAR myStart = FIND("DC=",[DN],,-99)
VAR myEnd = IF(myStart=-99,-99,FIND("DC=",[DN],myStart+3,-99))
RETURN IF(myEnd=-99,BLANK(),MID([DN],myStart+3,myEnd-myStart-4))

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


4 REPLIES 4
Super User
Super User

Re: Extracting First DN out of Distinguished Name

Column = 
VAR myStart = FIND("DC=",[DN])
VAR myEnd = FIND("DC=",[DN],myStart+3)
RETURN MID([DN],myStart+3,myEnd-myStart-4)

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


jvanmeter Frequent Visitor
Frequent Visitor

Re: Extracting First DN out of Distinguished Name

I get this error, "The search TExt Provided to function 'FIND' could not be found in the given text.

 

@Edit

 

It does not like FIND("DC=",[distinguishedName]) but does accept FIND("DC=",[distinguishedName],1,0) for whatever reason that is whats throwing the error

 

Super User
Super User

Re: Extracting First DN out of Distinguished Name

OK, that means that some of your values don't have a DC= in them so you will want to use the "Not Found Value" parameter of FIND. https://msdn.microsoft.com/en-us/query-bi/dax/find-function-dax

 

So, something like:

 

Column = 
VAR myStart = FIND("DC=",[DN],,-99)
VAR myEnd = IF(myStart=-99,-99,FIND("DC=",[DN],myStart+3,-99))
RETURN IF(myEnd=-99,BLANK(),MID([DN],myStart+3,myEnd-myStart-4))

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Highlighted
jvanmeter Frequent Visitor
Frequent Visitor

Re: Extracting First DN out of Distinguished Name

That works Perfectly. Thank you.