USERNAME() and USERPRINCIPALNAME() can be used in various ways.
However, In this post, we will look at two Power BI functions, USERNAME() and USERPRINCIPALNAME(), to extract either the first name or the user’s last name see how they differ when using Power BI Desktop and Power BI Service.
For this to work, we will need to make use of various string manipulation functions such as LEFT(), RIGHT(), LEN(), and FIND(). Apart from that, you will need to install the Power BI desktop.
Let us first go over these functions that we will be using to get to our result. If you want to go over the code directly, then please free to go over it at the end of this post.
Let us begin.
Username(): Returns the domain name and user name of the current connection with the format of domain-name\user-name. This function does not take any parameter. It returns the username from the credentials given to the system at connection time.
Left(): Returns the specified number of characters from the start of a text string. This returns a text string containing the specified right-most characters.
This function fetches the number of characters starting from left.
Right(): Returns the specified number of characters from the end of a text string. This returns a text string containing the specified right-most characters. Think of this as an equal and opposite of the LEFT() function.
This function fetches the number of characters starting from right.
LEN(): Returns the number of characters in a text string. This returns the number of characters in the text string.
Personally speaking, this is my favorite function as once I know the length of any string, we then have the power to manipulate and extract the data as we see fit.
FIND(): Returns the starting position of one text string within another text string. FIND is case-sensitive and accent-sensitive. This returns the starting position of the text string that we want to find. There are a few other functions that we can use, which can get us the same result as FIND(). But that is a topic for some other time.
Now that we have these functions handy, let us begin.
When we use USERNAME() in the Power BI desktop, it will return the domain name and username from the system’s credentials at connection time. One point to be noted here is that this function DOES NOT take any parameters. For example, if I was to use this function on the Power BI desktop, I will see Domain\username. However, when I publish the report, I will see a fully qualifies username such as firstname.lastname@example.org.
So here is a thought, how does one extract just the username when the position of username changes depending on the tool (Desktop vs. Service).
The trick lies in the fact that we will use Find() within the IF function to check for either “\” or “@” and then, based on the position of this separator( “\ or @”), we will be able to get the right result.
In the next post, I will go over this formula in detail as this can then modified to use in case when our usernames are email@example.com or firstname.lastname@example.org or LFname@domail.com.
Regardless of how your company uses the username, using this code and some fancy manipulations, you will extract the information you need.
The entire code is as follows and we will go over this logic in the next post.
MyUsername = VAR myusername = USERNAME () VAR findmyatrate = "@" VAR findmyslash = "\" VAR totallen = LEN ( USERNAME () ) VAR myname = IF ( FIND ( findmyatrate, myusername, 1, 0 ) = 0, RIGHT ( myusername, totallen - FIND ( findmyslash, myusername, 1 ) ), LEFT ( myusername, FIND ( findmyatrate, myusername, 1 ) - 1 ) ) VAR capfirstchar = UPPER ( LEFT ( myname, 1 ) ) VAR addremchar = LOWER ( RIGHT ( myname, LEN ( myname ) - 1 ) ) VAR comboth = capfirstchar & addremchar RETURN "Welcome : " & comboth
We followed this:
- We checked for the @ or \ in the name. (Why?? explanation is in the next post).
- Based on our find (FIND()), we extracted the name.
- We capitalized the First character of the last name and concatenated the rest.
Power BI USERNAME() is a function that can get the logged-in username. However, the result of this function varies depending on the tool/component of Power BI. In Power BI Desktop, the result is different than what we will see in the Power BI Service.
Stay tuned for a detailed explanation for this code. Hope you liked it.
Categories: String Manipulation