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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
jvanmeter
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

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

 

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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

 

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

 

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

That works Perfectly. Thank you.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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