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.

Categories: String Manipulation

Tags: Power BI, Power BI USERNAME(), Username()