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, they differ when using Power BI Desktop and Power BI Service.

We will pick up where we left off in part 1 of this post. As promised, here is the explanation in detail.

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

The behavior of these functions varies. In Power BI Desktop, it shows the local machine name\user name, for example, domain\username. You can find yours easily by going to cmd and typing in whoami. If I was to use \ as a means to find the username, then in Power BI Service, I will have a problem. This is because, in the Power BI Service, it shows username@domain.com. To accommodate for this, we need to search(find) for the index of @.

Once we know this, the rest of the work is as easy as finding the index value of either @ or \ and counting the correct number of characters from it. But wait, there is more.

Though we know that the behavior is different in Desktop and Service, we also need to ensure that we use both right and left functions. this is because the user’s name itself will shift the position. As you can see in the example above when in Desktop, the username is on the right (meaning after the \), and in Service, the username is on the left (meaning before the @)

Once this is done, it is just a matter of checking from which starting position the Find function returns a 0. Wrap this into an IF statement, and it will do the trick. At this point, all the heavy lifting is done.

To capitalize the first character, once again use left but wrap it around the upper function. This will change the case sensitivity to the upper case. Do the same for the rest of the characters and make when lower.

Finally, return the function and add a “welcome” or any other greeting if you so desire it, and now you can greet your users who logged in with a nice welcome message.

Though, I enjoyed working on this. There are a few things that I realized, and you will have to adjust your logic accordingly. The biggest adjustment will need to be made when you do have firstname.lastname@domain.com. Here you will need to extract the first name based on the first occurring. (period) and then do the rest. Overall the same logical reasoning should work just fine.

I hope you liked this. Let me know what you think or if you have a different approach to this.

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 username@domain.com.

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 abc.def@xyz.com or lastname.firstname@domain.com 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:

1. We checked for the @ or \ in the name. (Why?? explanation is in the next post).
2. Based on our find (FIND()), we extracted the name.
3. 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.